Paytm: Runner-Up Movie by Seats Sold in Each City

Medium
856.

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.

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

ColumnTypeValues
booking_idint
cityvarchar
movievarchar
show_datedate
seatsint
statusenumbooked, cancelled

Primary key: booking_id.

One row per ticket order; seats is how many seats that order holds.

Example

The rows must come back in the order shown.

MovieBooking

booking_idcitymovieshow_dateseatsstatus
1MumbaiMonsoon Express2025-03-074booked
2MumbaiMonsoon Express2025-03-085booked
3MumbaiTiger Valley2025-03-077booked
4MumbaiLast Over2025-03-083booked
5MumbaiLast Over2025-03-094booked
6MumbaiNeon Nights2025-03-086booked
7MumbaiNeon Nights2025-03-098cancelled
8PuneChai Pe Charcha2025-03-072booked
9PuneLast Over2025-03-085booked
10PuneNeon Nights2025-03-091booked
4 more rows

Output

cityrunner_up_seatsrunner_up_movies
IndoreNULLNULL
KochiNULLNULL
Mumbai7Last Over, Tiger Valley
Pune2Chai Pe Charcha
SuratNULLNULL
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: 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

ColumnType
booking_idint
cityvarchar
movievarchar
show_datedate
seatsint
statusenum(booked, cancelled)

Primary key: booking_id.

One row per ticket order; seats is how many seats that order holds.

Examples

Example 1

MovieBooking

booking_idcitymovieshow_dateseatsstatus
1MumbaiMonsoon Express2025-03-074booked
2MumbaiMonsoon Express2025-03-085booked
3MumbaiTiger Valley2025-03-077booked
4MumbaiLast Over2025-03-083booked
5MumbaiLast Over2025-03-094booked
6MumbaiNeon Nights2025-03-086booked
7MumbaiNeon Nights2025-03-098cancelled
8PuneChai Pe Charcha2025-03-072booked
9PuneLast Over2025-03-085booked
10PuneNeon Nights2025-03-091booked
11KochiKesari Dawn2025-03-084booked
12IndoreTiger Valley2025-03-073booked
13IndoreLast Over2025-03-073booked
14SuratTiger Valley2025-03-096cancelled

Output

cityrunner_up_seatsrunner_up_movies
IndoreNULLNULL
KochiNULLNULL
Mumbai7Last Over, Tiger Valley
Pune2Chai Pe Charcha
SuratNULLNULL

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.city

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