Paytm: Daily Seats Sold for Every Day of a Movie's Run
The movie-ticketing team plots each film's sales curve for distributors.
- Difficulty: Hard
- Topics: Subqueries, Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #869
Problem statement
The movie-ticketing team plots each film's sales curve for distributors. A day with no tickets must still be on the chart as a 0, or the line jumps over the dip.
For every movie, list every date from its release_date to its last_show_date, both inclusive, with movie_id, show_date, seats_sold (the seats of its booked tickets for shows on that date, 0 if none) and running_seats (seats sold from the release date up to and including that date). Cancelled tickets never count. Every ticket's show_date lies inside its movie's run, and no run is longer than 20 days. Return the rows ordered by movie_id, then show_date.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: Movie
| Column | Type |
|---|---|
| movie_id | int |
| title | varchar |
| release_date | date |
| last_show_date | date |
Primary key: movie_id.
One row per film listed on the app; last_show_date is never before release_date.
Table: TicketSale
| Column | Type |
|---|---|
| sale_id | int |
| movie_id | int |
| show_date | date |
| seats | int |
| status | enum(booked, cancelled) |
Primary key: sale_id.
One row per ticket order; show_date is the date of the show, not of the purchase.
Examples
Example 1
Movie
| movie_id | title | release_date | last_show_date |
|---|---|---|---|
| 1 | Monsoon Express | 2025-03-07 | 2025-03-11 |
| 2 | Kesari Dawn | 2025-03-09 | 2025-03-09 |
| 3 | Neon Nights | 2025-03-10 | 2025-03-11 |
TicketSale
| sale_id | movie_id | show_date | seats | status |
|---|---|---|---|---|
| 1 | 1 | 2025-03-07 | 4 | booked |
| 2 | 1 | 2025-03-07 | 2 | booked |
| 3 | 1 | 2025-03-08 | 5 | cancelled |
| 4 | 1 | 2025-03-10 | 3 | booked |
| 5 | 1 | 2025-03-11 | 1 | booked |
| 6 | 2 | 2025-03-09 | 6 | booked |
| 7 | 3 | 2025-03-11 | 2 | cancelled |
Output
| movie_id | show_date | seats_sold | running_seats |
|---|---|---|---|
| 1 | 2025-03-07 | 6 | 6 |
| 1 | 2025-03-08 | 0 | 6 |
| 1 | 2025-03-09 | 0 | 6 |
| 1 | 2025-03-10 | 3 | 9 |
| 1 | 2025-03-11 | 1 | 10 |
| 2 | 2025-03-09 | 6 | 6 |
| 3 | 2025-03-10 | 0 | 0 |
| 3 | 2025-03-11 | 0 | 0 |
How to solve Paytm: Daily Seats Sold for Every Day of a Movie's Run
The difficulty is that a day with no booked tickets leaves no row anywhere — GROUP BY show_date over the sales can only return days that sold something. So the dates have to be generated before the sales are attached. A recursive CTE does it per movie: the anchor row is the release date, and the recursive step adds one day with DATE_ADD(show_date, INTERVAL 1 DAY) while the date is still before last_show_date. A movie whose run is a single day stops at the anchor; DATE_ADD handles month ends, so a run from 26 February to 3 March produces every day in between.
Separately, booked seats are summed per (movie, show date) — cancelled orders are filtered first, so Monsoon Express's cancelled 8 March order leaves that day at 0. LEFT JOINing these sums to the generated calendar keeps every date, and COALESCE(…, 0) turns the empty days into 0. Neon Nights, whose only order was cancelled, still gets both of its days at 0.
The running total is then SUM(seats_sold) OVER (PARTITION BY movie_id ORDER BY show_date ROWS UNBOUNDED PRECEDING) over the filled rows, so it stays flat across the dips.
The alternative generates the calendar without recursion: a small table of offsets 0–19 CROSS JOINed to the movies, kept where the offset fits the run (this is why the statement bounds a run at 20 days), with the day's seats and the running total from correlated sums.
Reference solution (MySQL)
WITH RECURSIVE run_days AS (
SELECT movie_id, release_date AS show_date, last_show_date
FROM Movie
UNION ALL
SELECT movie_id, DATE_ADD(show_date, INTERVAL 1 DAY), last_show_date
FROM run_days
WHERE show_date < last_show_date
), daily AS (
SELECT movie_id, show_date, SUM(seats) AS seats
FROM TicketSale
WHERE status = 'booked'
GROUP BY movie_id, show_date
), filled AS (
SELECT r.movie_id, r.show_date, COALESCE(d.seats, 0) AS seats_sold
FROM run_days r
LEFT JOIN daily d ON d.movie_id = r.movie_id AND d.show_date = r.show_date
)
SELECT movie_id, show_date, seats_sold,
SUM(seats_sold) OVER (PARTITION BY movie_id ORDER BY show_date ROWS UNBOUNDED PRECEDING) AS running_seats
FROM filled
ORDER BY movie_id, show_dateAnother way
WITH digits AS (
SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
), days AS (
SELECT m.movie_id, DATE_ADD(m.release_date, INTERVAL g.n DAY) AS show_date
FROM Movie m CROSS JOIN digits g
WHERE g.n <= DATEDIFF(m.last_show_date, m.release_date)
)
SELECT x.movie_id, x.show_date,
IFNULL((SELECT SUM(t.seats) FROM TicketSale t WHERE t.movie_id = x.movie_id AND t.show_date = x.show_date AND t.status = 'booked'), 0) AS seats_sold,
IFNULL((SELECT SUM(t.seats) FROM TicketSale t WHERE t.movie_id = x.movie_id AND t.show_date <= x.show_date AND t.status = 'booked'), 0) AS running_seats
FROM days x
ORDER BY x.movie_id, x.show_date← Paytm Money: SIP Investor Retention Three Months Later by Cohort · Paytm: Merchant Loan Outstanding as of 31 January 2025 →