Workshop Track Revenue

A professional learning platform offers instructor-led workshops grouped into learning tracks. Organizations can reserve multiple seats in each workshop.

Table: workshops

+----------------+--------------+
| Column Name    | Type         |
+----------------+--------------+
| workshop_id    | integer      |
| workshop_name  | varchar(255) |
| learning_track | varchar(255) |
| seat_fee       | integer      |
+----------------+--------------+
+----------------+--------------+
| Column Name    | Type         |
+----------------+--------------+
| workshop_id    | integer      |
| workshop_name  | varchar(255) |
| learning_track | varchar(255) |
| seat_fee       | integer      |
+----------------+--------------+

workshop_id is the primary key. seat_fee is the price of one reserved seat, expressed in whole dollars.

Table: registrations

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| registration_id | integer |
| workshop_id     | integer |
| seats_booked    | integer |
+-----------------+---------+
+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| registration_id | integer |
| workshop_id     | integer |
| seats_booked    | integer |
+-----------------+---------+

registration_id is the primary key. workshop_id references workshops. Each row records one seat reservation for a workshop.

Calculate the total registration revenue for every learning track. Revenue from one registration is seat_fee * seats_booked.

Return learning_track and total_revenue, ordered by total_revenue from highest to lowest. When multiple tracks have the same revenue, order them alphabetically by learning_track. Do not return tracks whose workshops have no registrations.

Output

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| learning_track | varchar |
| total_revenue  | integer |
+----------------+---------+
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| learning_track | varchar |
| total_revenue  | integer |
+----------------+---------+

Example

Input: workshops

+-------------+----------------+----------------+----------+
| workshop_id | workshop_name  | learning_track | seat_fee |
+-------------+----------------+----------------+----------+
| 1           | Prompt Basics  | AI             | 160      |
| 2           | Vector Search  | AI             | 120      |
| 3           | Secure APIs    | Backend        | 1000     |
| 4           | Design Systems | Design         | 250      |
+-------------+----------------+----------------+----------+
+-------------+----------------+----------------+----------+
| workshop_id | workshop_name  | learning_track | seat_fee |
+-------------+----------------+----------------+----------+
| 1           | Prompt Basics  | AI             | 160      |
| 2           | Vector Search  | AI             | 120      |
| 3           | Secure APIs    | Backend        | 1000     |
| 4           | Design Systems | Design         | 250      |
+-------------+----------------+----------------+----------+

Input: registrations

+-----------------+-------------+--------------+
| registration_id | workshop_id | seats_booked |
+-----------------+-------------+--------------+
| 1               | 1           | 2            |
| 2               | 2           | 3            |
| 3               | 3           | 1            |
| 4               | 4           | 2            |
| 5               | 1           | 1            |
+-----------------+-------------+--------------+
+-----------------+-------------+--------------+
| registration_id | workshop_id | seats_booked |
+-----------------+-------------+--------------+
| 1               | 1           | 2            |
| 2               | 2           | 3            |
| 3               | 3           | 1            |
| 4               | 4           | 2            |
| 5               | 1           | 1            |
+-----------------+-------------+--------------+

Output:

+----------------+---------------+
| learning_track | total_revenue |
+----------------+---------------+
| Backend        | 1000          |
| AI             | 840           |
| Design         | 500           |
+----------------+---------------+
+----------------+---------------+
| learning_track | total_revenue |
+----------------+---------------+
| Backend        | 1000          |
| AI             | 840           |
| Design         | 500           |
+----------------+---------------+

Explanation: The AI track earns 3 * 160 + 3 * 120 = 840. Backend earns 1 * 1000 = 1000, and Design earns 2 * 250 = 500. The results are ordered by total revenue.