Zomato: Sold-Out Movie Shows on District — SQL Joins Problem
District sells movie tickets for cinemas. The programming team wants to know which shows sold every seat so it can add extra screenings of those films.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #1028
Problem statement
District sells movie tickets for cinemas. The programming team wants to know which shows sold every seat so it can add extra screenings of those films.
A show is sold out when the seats of its confirmed bookings add up to at least its capacity; cancelled bookings free their seats. Return show_id, movie_title and show_time of every sold-out show, in any order.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: MovieShow
| Column | Type |
|---|---|
| show_id | int |
| movie_title | varchar |
| screen | varchar |
| show_time | datetime |
| capacity | int |
Primary key: show_id.
One row per screening; capacity is the number of seats on sale.
Table: TicketBooking
| Column | Type |
|---|---|
| booking_id | int |
| show_id | int |
| seats | int |
| status | enum(confirmed, cancelled) |
Primary key: booking_id.
One row per booking of one or more seats in a show.
Examples
Example 1
MovieShow
| show_id | movie_title | screen | show_time | capacity |
|---|---|---|---|---|
| 1 | Monsoon Express | Audi 1 | 2024-08-15 18:30:00 | 6 |
| 2 | Monsoon Express | Audi 2 | 2024-08-15 21:00:00 | 4 |
| 3 | The Last Over | Audi 1 | 2024-08-16 14:00:00 | 5 |
| 4 | Chai and Chaos | Audi 3 | 2024-08-16 22:00:00 | 3 |
TicketBooking
| booking_id | show_id | seats | status |
|---|---|---|---|
| 1 | 1 | 4 | confirmed |
| 2 | 1 | 2 | confirmed |
| 3 | 2 | 4 | cancelled |
| 4 | 2 | 3 | confirmed |
| 5 | 3 | 5 | confirmed |
Output
| show_id | movie_title | show_time |
|---|---|---|
| 1 | Monsoon Express | 2024-08-15 18:30:00 |
| 3 | The Last Over | 2024-08-16 14:00:00 |
How to solve Zomato: Sold-Out Movie Shows on District
Join each show to its confirmed bookings and group by show. SUM(b.seats) is then the number of seats taken, and HAVING SUM(b.seats) >= s.capacity keeps the full shows. capacity is listed in GROUP BY so it can be used in HAVING under MySQL's ONLY_FULL_GROUP_BY mode; it is one value per show anyway.
Cancelled bookings are excluded in the join condition, so they free their seats as the statement says. A show whose bookings were all cancelled, or that sold nothing, finds no confirmed row, forms no group and is not reported — correct, since it has empty seats.
The alternative compares the capacity with a correlated SUM subquery. For a show with no confirmed booking the subquery returns NULL, capacity <= NULL is not true, and the show is dropped the same way. Either query reads each booking once; an index on TicketBooking(show_id) makes the subquery version a lookup per show.
Reference solution (MySQL)
SELECT s.show_id, s.movie_title, s.show_time
FROM MovieShow s
JOIN TicketBooking b ON b.show_id = s.show_id AND b.status = 'confirmed'
GROUP BY s.show_id, s.movie_title, s.show_time, s.capacity
HAVING SUM(b.seats) >= s.capacityAnother way
SELECT show_id, movie_title, show_time
FROM MovieShow s
WHERE capacity <= (SELECT SUM(seats) FROM TicketBooking b WHERE b.show_id = s.show_id AND b.status = 'confirmed')← Zomato: Feeding India Cities That Raised ₹50,000 · Zomato: Blinkit Store Items Below Their Reorder Level →