Box Office Revenue per Show — SQL Joins Problem
A box office records every booking of a show. A booking holds several seats and the amount paid for all of them; a cancelled booking was refunded in full.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #78
Problem statement
A box office records every booking of a show. A booking holds several seats and the amount paid for all of them; a cancelled booking was refunded in full.
For each show with at least one confirmed booking, return show_id, show_name, seats_sold (total seats over its confirmed bookings) and revenue (total amount_paid over them). Cancelled bookings count for nothing. Order by revenue descending, then show_id ascending.
Tables
Table: Performance
| Column | Type |
|---|---|
| show_id | int |
| show_name | varchar |
| venue | varchar |
Primary key: show_id.
One row per show on sale. Two shows can share a name (the same act on two nights).
Table: Booking
| Column | Type |
|---|---|
| booking_id | int |
| show_id | int |
| seats | int |
| amount_paid | int |
| booking_status | enum(confirmed, cancelled) |
Primary key: booking_id.
amount_paid is in rupees for all the booking's seats. Every show_id is in Performance.
Examples
Example 1
Performance
| show_id | show_name | venue |
|---|---|---|
| 1 | Comedy Premier League | Bengaluru |
| 2 | Indian Ocean Unplugged | Pune |
| 3 | Comedy Premier League | Bengaluru |
| 4 | Jazz by the Bay | Mumbai |
Booking
| booking_id | show_id | seats | amount_paid | booking_status |
|---|---|---|---|---|
| 1 | 1 | 2 | 1600 | confirmed |
| 2 | 1 | 4 | 3200 | confirmed |
| 3 | 2 | 3 | 4500 | confirmed |
| 4 | 3 | 6 | 4800 | cancelled |
| 5 | 3 | 2 | 1600 | confirmed |
| 6 | 2 | 2 | 3000 | cancelled |
| 7 | 4 | 1 | 1600 | confirmed |
Output
| show_id | show_name | seats_sold | revenue |
|---|---|---|---|
| 1 | Comedy Premier League | 6 | 4800 |
| 2 | Indian Ocean Unplugged | 3 | 4500 |
| 3 | Comedy Premier League | 2 | 1600 |
| 4 | Jazz by the Bay | 1 | 1600 |
How to solve Box Office Revenue per Show
Join each booking to its show, keep the confirmed ones, and sum per show. Because the join is an inner join and the filter removes cancelled bookings, a show with nothing confirmed has no rows left and drops out, as the statement requires.
Grouping by show_name alone would be wrong: in the example the comedy act plays shows 1 and 3, and their revenue must stay separate. Grouping by show_id, show_name keeps them apart and still allows the name in the select list under ONLY_FULL_GROUP_BY (the name is determined by the id anyway).
The alternative aggregates Booking first in a derived table and joins the small result to Performance. On a large bookings table that is often cheaper, because the join then touches one row per show instead of one per booking. Revenue ties are broken by show_id.
Reference solution (MySQL)
SELECT s.show_id, s.show_name, SUM(b.seats) AS seats_sold, SUM(b.amount_paid) AS revenue
FROM Performance s
JOIN Booking b ON b.show_id = s.show_id
WHERE b.booking_status = 'confirmed'
GROUP BY s.show_id, s.show_name
ORDER BY revenue DESC, s.show_idAnother way
SELECT s.show_id, s.show_name, t.seats_sold, t.revenue
FROM (SELECT show_id, SUM(seats) AS seats_sold, SUM(amount_paid) AS revenue FROM Booking WHERE booking_status = 'confirmed' GROUP BY show_id) t
JOIN Performance s ON s.show_id = t.show_id
ORDER BY t.revenue DESC, s.show_id← Polling Booth Turnout Bands · Library Cards Expiring in March 2025 →