Hotstar: Viewers Lost During Each Rain Delay

Hard
717.

Hotstar: Viewers Lost During Each Rain Delay

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

ColumnType
match_idint
taken_atdatetime
viewersbigint

Primary key (together): match_id, taken_at.

One concurrency sample of a match; at most one per match per moment.

Table: RainDelay

ColumnType
delay_idint
match_idint
started_atdatetime
ended_atdatetime

Primary key: delay_id.

One row per interruption; ended_at is after started_at. A match can have several.

Example

The rows must come back in the order shown.

ViewerSample

match_idtaken_atviewers
12025-04-20 20:00:0018000000
12025-04-20 20:10:0017500000
12025-04-20 20:20:0012000000
12025-04-20 20:50:0013000000
12025-04-20 21:00:0016000000
22025-04-20 19:40:009000000
22025-04-20 19:50:008000000
32025-04-20 20:30:0010000000
32025-04-20 21:45:0011000000

RainDelay

delay_idmatch_idstarted_atended_at
112025-04-20 20:10:002025-04-20 20:55:00
222025-04-20 19:30:002025-04-20 19:50:00
332025-04-20 21:00:002025-04-20 21:30:00
412025-04-20 21:05:002025-04-20 21:20:00

Output

delay_idmatch_idviewers_beforeviewers_afterviewers_lost
1117500000160000001500000
331000000011000000-1000000
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.

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

ColumnType
match_idint
taken_atdatetime
viewersbigint

Primary key: match_id, taken_at.

One concurrency sample of a match; at most one per match per moment.

Table: RainDelay

ColumnType
delay_idint
match_idint
started_atdatetime
ended_atdatetime

Primary key: delay_id.

One row per interruption; ended_at is after started_at. A match can have several.

Examples

Example 1

ViewerSample

match_idtaken_atviewers
12025-04-20 20:00:0018000000
12025-04-20 20:10:0017500000
12025-04-20 20:20:0012000000
12025-04-20 20:50:0013000000
12025-04-20 21:00:0016000000
22025-04-20 19:40:009000000
22025-04-20 19:50:008000000
32025-04-20 20:30:0010000000
32025-04-20 21:45:0011000000

RainDelay

delay_idmatch_idstarted_atended_at
112025-04-20 20:10:002025-04-20 20:55:00
222025-04-20 19:30:002025-04-20 19:50:00
332025-04-20 21:00:002025-04-20 21:30:00
412025-04-20 21:05:002025-04-20 21:20:00

Output

delay_idmatch_idviewers_beforeviewers_afterviewers_lost
1117500000160000001500000
331000000011000000-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_id

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

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