Zomato: Sold-Out Movie Shows on District

Easy
1028.

Zomato: Sold-Out Movie Shows on District

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

ColumnType
show_idint
movie_titlevarchar
screenvarchar
show_timedatetime
capacityint

Primary key: show_id.

One row per screening; capacity is the number of seats on sale.

Table: TicketBooking

ColumnTypeValues
booking_idint
show_idint
seatsint
statusenumconfirmed, cancelled

Primary key: booking_id.

One row per booking of one or more seats in a show.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

MovieShow

show_idmovie_titlescreenshow_timecapacity
1Monsoon ExpressAudi 12024-08-15 18:30:006
2Monsoon ExpressAudi 22024-08-15 21:00:004
3The Last OverAudi 12024-08-16 14:00:005
4Chai and ChaosAudi 32024-08-16 22:00:003

TicketBooking

booking_idshow_idseatsstatus
114confirmed
212confirmed
324cancelled
423confirmed
535confirmed

Output

show_idmovie_titleshow_time
1Monsoon Express2024-08-15 18:30:00
3The Last Over2024-08-16 14:00:00
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.

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

ColumnType
show_idint
movie_titlevarchar
screenvarchar
show_timedatetime
capacityint

Primary key: show_id.

One row per screening; capacity is the number of seats on sale.

Table: TicketBooking

ColumnType
booking_idint
show_idint
seatsint
statusenum(confirmed, cancelled)

Primary key: booking_id.

One row per booking of one or more seats in a show.

Examples

Example 1

MovieShow

show_idmovie_titlescreenshow_timecapacity
1Monsoon ExpressAudi 12024-08-15 18:30:006
2Monsoon ExpressAudi 22024-08-15 21:00:004
3The Last OverAudi 12024-08-16 14:00:005
4Chai and ChaosAudi 32024-08-16 22:00:003

TicketBooking

booking_idshow_idseatsstatus
114confirmed
212confirmed
324cancelled
423confirmed
535confirmed

Output

show_idmovie_titleshow_time
1Monsoon Express2024-08-15 18:30:00
3The Last Over2024-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.capacity

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