Box Office Revenue per Show

Easy
78.

Box Office Revenue per Show

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

ColumnType
show_idint
show_namevarchar
venuevarchar

Primary key: show_id.

One row per show on sale. Two shows can share a name (the same act on two nights).

Table: Booking

ColumnTypeValues
booking_idint
show_idint
seatsint
amount_paidint
booking_statusenumconfirmed, cancelled

Primary key: booking_id.

amount_paid is in rupees for all the booking's seats. Every show_id is in Performance.

Example

The rows must come back in the order shown.

Performance

show_idshow_namevenue
1Comedy Premier LeagueBengaluru
2Indian Ocean UnpluggedPune
3Comedy Premier LeagueBengaluru
4Jazz by the BayMumbai

Booking

booking_idshow_idseatsamount_paidbooking_status
1121600confirmed
2143200confirmed
3234500confirmed
4364800cancelled
5321600confirmed
6223000cancelled
7411600confirmed

Output

show_idshow_nameseats_soldrevenue
1Comedy Premier League64800
2Indian Ocean Unplugged34500
3Comedy Premier League21600
4Jazz by the Bay11600
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
show_idint
show_namevarchar
venuevarchar

Primary key: show_id.

One row per show on sale. Two shows can share a name (the same act on two nights).

Table: Booking

ColumnType
booking_idint
show_idint
seatsint
amount_paidint
booking_statusenum(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_idshow_namevenue
1Comedy Premier LeagueBengaluru
2Indian Ocean UnpluggedPune
3Comedy Premier LeagueBengaluru
4Jazz by the BayMumbai

Booking

booking_idshow_idseatsamount_paidbooking_status
1121600confirmed
2143200confirmed
3234500confirmed
4364800cancelled
5321600confirmed
6223000cancelled
7411600confirmed

Output

show_idshow_nameseats_soldrevenue
1Comedy Premier League64800
2Indian Ocean Unplugged34500
3Comedy Premier League21600
4Jazz by the Bay11600

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_id

Another 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 →