Hotstar: Biggest Over-on-Over Jump in Viewers — SQL Problem
The social team at Hotstar wants to know which moment of each match pulled the most people in.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #708
Problem statement
The social team at Hotstar wants to know which moment of each match pulled the most people in. Concurrency is sampled at the end of overs (not every over is sampled), in order of innings and then over; the jump at a sample is its viewers minus the viewers at the previous sample of the same match — across the innings break too. A match's first sample has no jump.
For every match with at least two samples, return the sample with the largest jump as match_id, innings, over_no and jump. The largest jump can be negative if a match only lost viewers. If several samples share the largest jump, return the earliest (lowest innings, then lowest over). Return the rows in any order.
A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.
Tables
Table: OverSnapshot
| Column | Type |
|---|---|
| match_id | int |
| innings | int |
| over_no | int |
| viewers | bigint |
Primary key: match_id, innings, over_no.
Concurrent viewers sampled at the end of over over_no (1–20) of innings 1 or 2.
Examples
Example 1
OverSnapshot
| match_id | innings | over_no | viewers |
|---|---|---|---|
| 1 | 1 | 5 | 9000000 |
| 1 | 1 | 10 | 11500000 |
| 1 | 2 | 1 | 12000000 |
| 1 | 2 | 18 | 14500000 |
| 2 | 1 | 6 | 7000000 |
| 2 | 1 | 20 | 6500000 |
| 2 | 2 | 5 | 6000000 |
| 3 | 1 | 1 | 5000000 |
Output
| match_id | innings | over_no | jump |
|---|---|---|---|
| 1 | 1 | 10 | 2500000 |
| 2 | 1 | 20 | -500000 |
How to solve Hotstar: Biggest Over-on-Over Jump in Viewers
Two window steps. First, LAG(viewers) OVER (PARTITION BY match_id ORDER BY innings, over_no) puts the previous sample's viewers beside each sample, and the difference is the jump. Partitioning by match alone, and ordering by innings then over, makes the first sample of the second innings compare with the last sample of the first — the chase starting is often the biggest moment. Missing overs do not matter: the previous sample is what counts.
Each match's first sample has a NULL jump and is filtered out, so a match with one sample has nothing left and is not listed. Then ROW_NUMBER() per match, ordered by jump DESC, innings, over_no, picks exactly one sample — the largest jump, and among equal jumps the earliest, as the statement asks. A match that only lost viewers still has a largest (least negative) jump.
The alternative does it without windows: a combined position innings * 100 + over_no finds the previous sample through a correlated MAX, and NOT EXISTS keeps the jump that no other sample of the match beats. It is quadratic per match; the window version sorts each match once.
Reference solution (MySQL)
WITH jumps AS (
SELECT match_id, innings, over_no,
viewers - LAG(viewers) OVER (PARTITION BY match_id ORDER BY innings, over_no) AS jump
FROM OverSnapshot
),
ranked AS (
SELECT match_id, innings, over_no, jump,
ROW_NUMBER() OVER (PARTITION BY match_id ORDER BY jump DESC, innings, over_no) AS rn
FROM jumps
WHERE jump IS NOT NULL
)
SELECT match_id, innings, over_no, jump
FROM ranked
WHERE rn = 1Another way
WITH k AS (
SELECT match_id, innings, over_no, viewers, innings * 100 + over_no AS pos FROM OverSnapshot
),
j AS (
SELECT c.match_id, c.innings, c.over_no, c.pos, c.viewers - p.viewers AS jump
FROM k c
JOIN k p ON p.match_id = c.match_id
AND p.pos = (SELECT MAX(q.pos) FROM k q WHERE q.match_id = c.match_id AND q.pos < c.pos)
)
SELECT match_id, innings, over_no, jump
FROM j
WHERE NOT EXISTS (
SELECT 1 FROM j b
WHERE b.match_id = j.match_id AND (b.jump > j.jump OR (b.jump = j.jump AND b.pos < j.pos))
)← Hotstar: Commentary Language Split per Match · Hotstar: Subscribers Who Only Ever Watch Live Sport →