Paytm: Daily Seats Sold for Every Day of a Movie's Run

Hard
869.

Paytm: Daily Seats Sold for Every Day of a Movie's Run

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

ColumnType
movie_idint
titlevarchar
release_datedate
last_show_datedate

Primary key: movie_id.

One row per film listed on the app; last_show_date is never before release_date.

Table: TicketSale

ColumnTypeValues
sale_idint
movie_idint
show_datedate
seatsint
statusenumbooked, cancelled

Primary key: sale_id.

One row per ticket order; show_date is the date of the show, not of the purchase.

Example

The rows must come back in the order shown.

Movie

movie_idtitlerelease_datelast_show_date
1Monsoon Express2025-03-072025-03-11
2Kesari Dawn2025-03-092025-03-09
3Neon Nights2025-03-102025-03-11

TicketSale

sale_idmovie_idshow_dateseatsstatus
112025-03-074booked
212025-03-072booked
312025-03-085cancelled
412025-03-103booked
512025-03-111booked
622025-03-096booked
732025-03-112cancelled

Output

movie_idshow_dateseats_soldrunning_seats
12025-03-0766
12025-03-0806
12025-03-0906
12025-03-1039
12025-03-11110
22025-03-0966
32025-03-1000
32025-03-1100
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.

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

ColumnType
movie_idint
titlevarchar
release_datedate
last_show_datedate

Primary key: movie_id.

One row per film listed on the app; last_show_date is never before release_date.

Table: TicketSale

ColumnType
sale_idint
movie_idint
show_datedate
seatsint
statusenum(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_idtitlerelease_datelast_show_date
1Monsoon Express2025-03-072025-03-11
2Kesari Dawn2025-03-092025-03-09
3Neon Nights2025-03-102025-03-11

TicketSale

sale_idmovie_idshow_dateseatsstatus
112025-03-074booked
212025-03-072booked
312025-03-085cancelled
412025-03-103booked
512025-03-111booked
622025-03-096booked
732025-03-112cancelled

Output

movie_idshow_dateseats_soldrunning_seats
12025-03-0766
12025-03-0806
12025-03-0906
12025-03-1039
12025-03-11110
22025-03-0966
32025-03-1000
32025-03-1100

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_date

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