Hotstar: Viewers Lost During Each Rain Delay — SQL Problem
When rain stops play, viewers drift away, and Hotstar's programming team decides what to show in the gap by how many it lost last time.
- Difficulty: Hard
- Topics: Subqueries, Joins, Dates
- Dialect: MySQL
- Problem: #717
Problem statement
When rain stops play, viewers drift away, and Hotstar's programming team decides what to show in the gap by how many it lost last time. Concurrency is sampled at irregular times. For a delay, viewers before is the sample of that match taken at or before the delay started (the latest such one), and viewers after is the sample taken at or after the delay ended (the earliest such one).
Return delay_id, match_id, viewers_before, viewers_after and viewers_lost (= before − after, negative if the audience grew) for every delay that has both a before and an after sample. Order by delay_id.
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: ViewerSample
| Column | Type |
|---|---|
| match_id | int |
| taken_at | datetime |
| viewers | bigint |
Primary key: match_id, taken_at.
One concurrency sample of a match; at most one per match per moment.
Table: RainDelay
| Column | Type |
|---|---|
| delay_id | int |
| match_id | int |
| started_at | datetime |
| ended_at | datetime |
Primary key: delay_id.
One row per interruption; ended_at is after started_at. A match can have several.
Examples
Example 1
ViewerSample
| match_id | taken_at | viewers |
|---|---|---|
| 1 | 2025-04-20 20:00:00 | 18000000 |
| 1 | 2025-04-20 20:10:00 | 17500000 |
| 1 | 2025-04-20 20:20:00 | 12000000 |
| 1 | 2025-04-20 20:50:00 | 13000000 |
| 1 | 2025-04-20 21:00:00 | 16000000 |
| 2 | 2025-04-20 19:40:00 | 9000000 |
| 2 | 2025-04-20 19:50:00 | 8000000 |
| 3 | 2025-04-20 20:30:00 | 10000000 |
| 3 | 2025-04-20 21:45:00 | 11000000 |
RainDelay
| delay_id | match_id | started_at | ended_at |
|---|---|---|---|
| 1 | 1 | 2025-04-20 20:10:00 | 2025-04-20 20:55:00 |
| 2 | 2 | 2025-04-20 19:30:00 | 2025-04-20 19:50:00 |
| 3 | 3 | 2025-04-20 21:00:00 | 2025-04-20 21:30:00 |
| 4 | 1 | 2025-04-20 21:05:00 | 2025-04-20 21:20:00 |
Output
| delay_id | match_id | viewers_before | viewers_after | viewers_lost |
|---|---|---|---|---|
| 1 | 1 | 17500000 | 16000000 | 1500000 |
| 3 | 3 | 10000000 | 11000000 | -1000000 |
How to solve Hotstar: Viewers Lost During Each Rain Delay
Each delay needs two as-of lookups against the sample table: the last sample at or before the start, and the first at or after the end. The cleanest way to say "the last sample at or before" in SQL is a correlated subquery ordered by time and limited to one row; mirror it for the after side. The boundaries are inclusive in both directions, so delay 1's start at exactly 20:10 uses the 20:10 sample, while the samples taken during the delay (20:20, 20:50) are on neither side.
A delay with no sample before it (delay 2 began before match 2's first sample) or none after it (delay 4 ended after match 1's last sample) gets a NULL and is filtered out. viewers_lost is a plain difference and can be negative — match 3 gained viewers across its delay, which is still a result.
The first alternative finds each side's timestamp with MAX/MIN and joins the sample row back on the primary key — the inner joins drop incomplete delays by themselves. The second ranks candidate samples per delay with ROW_NUMBER(). All are a range probe per delay with an index on (match_id, taken_at).
Reference solution (MySQL)
SELECT delay_id, match_id, viewers_before, viewers_after,
viewers_before - viewers_after AS viewers_lost
FROM (
SELECT d.delay_id, d.match_id,
(SELECT s.viewers FROM ViewerSample s
WHERE s.match_id = d.match_id AND s.taken_at <= d.started_at
ORDER BY s.taken_at DESC LIMIT 1) AS viewers_before,
(SELECT s.viewers FROM ViewerSample s
WHERE s.match_id = d.match_id AND s.taken_at >= d.ended_at
ORDER BY s.taken_at LIMIT 1) AS viewers_after
FROM RainDelay d
) x
WHERE viewers_before IS NOT NULL AND viewers_after IS NOT NULL
ORDER BY delay_idAnother way
SELECT d.delay_id, d.match_id, b.viewers AS viewers_before, a.viewers AS viewers_after,
b.viewers - a.viewers AS viewers_lost
FROM RainDelay d
JOIN ViewerSample b ON b.match_id = d.match_id
AND b.taken_at = (SELECT MAX(taken_at) FROM ViewerSample WHERE match_id = d.match_id AND taken_at <= d.started_at)
JOIN ViewerSample a ON a.match_id = d.match_id
AND a.taken_at = (SELECT MIN(taken_at) FROM ViewerSample WHERE match_id = d.match_id AND taken_at >= d.ended_at)
ORDER BY d.delay_idAnother way
WITH before_s AS (
SELECT d.delay_id, s.viewers, ROW_NUMBER() OVER (PARTITION BY d.delay_id ORDER BY s.taken_at DESC) AS rn
FROM RainDelay d JOIN ViewerSample s ON s.match_id = d.match_id AND s.taken_at <= d.started_at
),
after_s AS (
SELECT d.delay_id, s.viewers, ROW_NUMBER() OVER (PARTITION BY d.delay_id ORDER BY s.taken_at) AS rn
FROM RainDelay d JOIN ViewerSample s ON s.match_id = d.match_id AND s.taken_at >= d.ended_at
)
SELECT d.delay_id, d.match_id, b.viewers AS viewers_before, a.viewers AS viewers_after, b.viewers - a.viewers AS viewers_lost
FROM RainDelay d
JOIN before_s b ON b.delay_id = d.delay_id AND b.rn = 1
JOIN after_s a ON a.delay_id = d.delay_id AND a.rn = 1
ORDER BY d.delay_id← Hotstar: Peak of Three-Sample Smoothed Concurrency per Match · Hotstar: 90th-Percentile Video Start Time by CDN →