Paytm: Runner-Up Movie by Seats Sold in Each City
The movie-ticketing team already promotes each city's best-selling film; this week it wants to give the runner-up a banner too.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #856
Problem statement
The movie-ticketing team already promotes each city's best-selling film; this week it wants to give the runner-up a banner too.
Total each movie's seats per city over its booked bookings (cancelled ones never count). The runner-up total is the second-highest distinct total in the city; every movie with that total is a runner-up. Return one row for every city that appears in MovieBooking, with city, runner_up_seats and runner_up_movies (the runner-up movies' names in alphabetical order joined with ', '). When a city has no second distinct total — one movie, movies tied at the top, or only cancelled bookings — both columns are NULL. Return the rows ordered by city.
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: MovieBooking
| Column | Type |
|---|---|
| booking_id | int |
| city | varchar |
| movie | varchar |
| show_date | date |
| seats | int |
| status | enum(booked, cancelled) |
Primary key: booking_id.
One row per ticket order; seats is how many seats that order holds.
Examples
Example 1
MovieBooking
| booking_id | city | movie | show_date | seats | status |
|---|---|---|---|---|---|
| 1 | Mumbai | Monsoon Express | 2025-03-07 | 4 | booked |
| 2 | Mumbai | Monsoon Express | 2025-03-08 | 5 | booked |
| 3 | Mumbai | Tiger Valley | 2025-03-07 | 7 | booked |
| 4 | Mumbai | Last Over | 2025-03-08 | 3 | booked |
| 5 | Mumbai | Last Over | 2025-03-09 | 4 | booked |
| 6 | Mumbai | Neon Nights | 2025-03-08 | 6 | booked |
| 7 | Mumbai | Neon Nights | 2025-03-09 | 8 | cancelled |
| 8 | Pune | Chai Pe Charcha | 2025-03-07 | 2 | booked |
| 9 | Pune | Last Over | 2025-03-08 | 5 | booked |
| 10 | Pune | Neon Nights | 2025-03-09 | 1 | booked |
| 11 | Kochi | Kesari Dawn | 2025-03-08 | 4 | booked |
| 12 | Indore | Tiger Valley | 2025-03-07 | 3 | booked |
| 13 | Indore | Last Over | 2025-03-07 | 3 | booked |
| 14 | Surat | Tiger Valley | 2025-03-09 | 6 | cancelled |
Output
| city | runner_up_seats | runner_up_movies |
|---|---|---|
| Indore | NULL | NULL |
| Kochi | NULL | NULL |
| Mumbai | 7 | Last Over, Tiger Valley |
| Pune | 2 | Chai Pe Charcha |
| Surat | NULL | NULL |
How to solve Paytm: Runner-Up Movie by Seats Sold in Each City
First the totals: booked seats per (city, movie). Cancelled orders are filtered before summing, so Neon Nights' cancelled 8-seat order in Mumbai does not lift it.
"Second-highest distinct total" is what DENSE_RANK() measures: equal totals share a rank and the next total gets the next integer. Mumbai's 9, 7, 7, 6 rank 1, 2, 2, 3, so both 7s are runner-ups. RANK() would give 1, 2, 2, 4 and would also work here, but for a tie at the top it gives 1, 1, 3 — no rank 2 although a second distinct total exists. ROW_NUMBER() would pick one tied movie arbitrarily.
The NULL rows are the part that is easy to lose. Filtering pos = 2 drops every city without a runner-up — Kochi (one movie), Indore (two movies tied at the top, so only one distinct total) and Surat (only a cancelled booking, so no totals at all). Starting from the distinct cities of the whole table and LEFT JOINing the rank-2 rows keeps them with NULLs, and grouping by city with MAX and an ordered GROUP_CONCAT folds the tied runner-ups into one line.
The alternative finds the second distinct total as the largest total below the city's maximum, and lists its movies with a correlated GROUP_CONCAT.
Reference solution (MySQL)
WITH totals AS (
SELECT city, movie, SUM(seats) AS seats_sold
FROM MovieBooking
WHERE status = 'booked'
GROUP BY city, movie
), ranked AS (
SELECT city, movie, seats_sold,
DENSE_RANK() OVER (PARTITION BY city ORDER BY seats_sold DESC) AS pos
FROM totals
), cities AS (
SELECT DISTINCT city FROM MovieBooking
)
SELECT c.city, MAX(r.seats_sold) AS runner_up_seats,
GROUP_CONCAT(r.movie ORDER BY r.movie SEPARATOR ', ') AS runner_up_movies
FROM cities c
LEFT JOIN ranked r ON r.city = c.city AND r.pos = 2
GROUP BY c.city
ORDER BY c.cityAnother way
WITH totals AS (SELECT city, movie, SUM(seats) AS seats_sold FROM MovieBooking WHERE status = 'booked' GROUP BY city, movie),
second AS (
SELECT a.city, MAX(a.seats_sold) AS s FROM totals a
WHERE a.seats_sold < (SELECT MAX(b.seats_sold) FROM totals b WHERE b.city = a.city)
GROUP BY a.city
)
SELECT c.city, s.s AS runner_up_seats,
(SELECT GROUP_CONCAT(t.movie ORDER BY t.movie SEPARATOR ', ') FROM totals t WHERE t.city = c.city AND t.seats_sold = s.s) AS runner_up_movies
FROM (SELECT DISTINCT city FROM MovieBooking) c
LEFT JOIN second s ON s.city = c.city
ORDER BY c.city← Paytm: Digital Gold Customers Holding at Least One Gram · Paytm: Users Who Paid Electricity in Every Month of Q1 2025 →