Hotstar: Stage Times of Slow Highlights Clips — SQL Problem
During a live match, every wicket and six becomes a highlights clip on Hotstar within minutes.
- Difficulty: Hard
- Topics: Conditional Logic, Aggregation, Dates
- Dialect: MySQL
- Problem: #721
Problem statement
During a live match, every wicket and six becomes a highlights clip on Hotstar within minutes. Each clip goes through four stages — ingested (the broadcast segment arrives), cut (an editor trims it), encoded and published — and every attempt at a stage writes one row to ClipEvent. A failed attempt is retried, so a stage can appear more than once. The clip's clock starts at its first ingested event; every later stage is timed by its last attempt (the one that succeeded).
For every clip that has all four stages and whose total time from first ingest to last publish is more than 240 seconds, return clip_id, match_id, cut_secs (first ingest → last cut), encode_secs (last cut → last encode), publish_secs (last encode → last publish) and total_secs (first ingest → last publish), all in whole seconds. Order by total_secs descending, then clip_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: ClipEvent
| Column | Type |
|---|---|
| event_id | int |
| clip_id | int |
| match_id | int |
| stage | enum(ingested, cut, encoded, published) |
| logged_at | datetime |
Primary key: event_id.
One row per attempt at a pipeline stage. Every event of a clip carries the same match_id, and a clip's stages happen in pipeline order.
Examples
Example 1
ClipEvent
| event_id | clip_id | match_id | stage | logged_at |
|---|---|---|---|---|
| 1 | 1 | 7 | ingested | 2025-04-12 20:00:00 |
| 2 | 1 | 7 | ingested | 2025-04-12 20:00:20 |
| 3 | 1 | 7 | cut | 2025-04-12 20:01:10 |
| 4 | 1 | 7 | encoded | 2025-04-12 20:02:30 |
| 5 | 1 | 7 | encoded | 2025-04-12 20:04:00 |
| 6 | 1 | 7 | published | 2025-04-12 20:04:45 |
| 7 | 2 | 7 | ingested | 2025-04-12 20:10:00 |
| 8 | 2 | 7 | cut | 2025-04-12 20:10:50 |
| 9 | 2 | 7 | encoded | 2025-04-12 20:11:40 |
| 10 | 2 | 7 | published | 2025-04-12 20:12:00 |
| 11 | 3 | 8 | ingested | 2025-04-12 20:15:00 |
| 12 | 3 | 8 | cut | 2025-04-12 20:16:00 |
| 13 | 3 | 8 | cut | 2025-04-12 20:17:30 |
| 14 | 3 | 8 | encoded | 2025-04-12 20:19:00 |
| 15 | 3 | 8 | published | 2025-04-12 20:21:00 |
| 16 | 4 | 8 | ingested | 2025-04-12 20:22:00 |
| 17 | 4 | 8 | cut | 2025-04-12 20:23:00 |
| 18 | 4 | 8 | encoded | 2025-04-12 20:24:00 |
| 19 | 5 | 8 | ingested | 2025-04-12 20:30:00 |
| 20 | 5 | 8 | cut | 2025-04-12 20:31:00 |
| 21 | 5 | 8 | encoded | 2025-04-12 20:33:00 |
| 22 | 5 | 8 | published | 2025-04-12 20:34:00 |
Output
| clip_id | match_id | cut_secs | encode_secs | publish_secs | total_secs |
|---|---|---|---|---|---|
| 3 | 8 | 150 | 90 | 120 | 360 |
| 1 | 7 | 70 | 170 | 45 | 285 |
How to solve Hotstar: Stage Times of Slow Highlights Clips
The log is long: one row per attempt, so the times of a clip's stages sit on different rows and cannot be subtracted directly. Pivot it to wide with conditional aggregation: group by clip, and for each stage take an aggregate of a CASE that yields logged_at only on that stage's rows. The aggregate also settles the retries: the clock starts at the first ingest, so that column is MIN(CASE …), while a retried cut, encode or publish is timed by its last, successful attempt, so those are MAX(CASE …). In the example, clip 1's second ingest and clip 3's first cut are both ignored for this reason.
With one row per clip, each stage duration is TIMESTAMPDIFF(SECOND, …) between neighbouring columns, and the total is first ingest to last publish (which equals the sum of the three). A clip that never reached a stage has a NULL in its column — clip 4 was never published — so the filter requires all four. The threshold is strict: clip 5 took exactly 240 seconds and is not listed.
The alternative builds the same four timestamps as four grouped derived tables, one per stage, and joins them on the clip; the inner joins drop incomplete clips by themselves. The pivot reads the log once; the join version reads it four times.
Reference solution (MySQL)
WITH wide AS (
SELECT clip_id, match_id,
MIN(CASE WHEN stage = 'ingested' THEN logged_at END) AS ingested_at,
MAX(CASE WHEN stage = 'cut' THEN logged_at END) AS cut_at,
MAX(CASE WHEN stage = 'encoded' THEN logged_at END) AS encoded_at,
MAX(CASE WHEN stage = 'published' THEN logged_at END) AS published_at
FROM ClipEvent
GROUP BY clip_id, match_id
)
SELECT clip_id, match_id,
TIMESTAMPDIFF(SECOND, ingested_at, cut_at) AS cut_secs,
TIMESTAMPDIFF(SECOND, cut_at, encoded_at) AS encode_secs,
TIMESTAMPDIFF(SECOND, encoded_at, published_at) AS publish_secs,
TIMESTAMPDIFF(SECOND, ingested_at, published_at) AS total_secs
FROM wide
WHERE ingested_at IS NOT NULL AND cut_at IS NOT NULL
AND encoded_at IS NOT NULL AND published_at IS NOT NULL
AND TIMESTAMPDIFF(SECOND, ingested_at, published_at) > 240
ORDER BY total_secs DESC, clip_idAnother way
SELECT i.clip_id, i.match_id,
TIMESTAMPDIFF(SECOND, i.t, c.t) AS cut_secs,
TIMESTAMPDIFF(SECOND, c.t, e.t) AS encode_secs,
TIMESTAMPDIFF(SECOND, e.t, p.t) AS publish_secs,
TIMESTAMPDIFF(SECOND, i.t, p.t) AS total_secs
FROM (SELECT clip_id, match_id, MIN(logged_at) AS t FROM ClipEvent WHERE stage = 'ingested' GROUP BY clip_id, match_id) i
JOIN (SELECT clip_id, MAX(logged_at) AS t FROM ClipEvent WHERE stage = 'cut' GROUP BY clip_id) c ON c.clip_id = i.clip_id
JOIN (SELECT clip_id, MAX(logged_at) AS t FROM ClipEvent WHERE stage = 'encoded' GROUP BY clip_id) e ON e.clip_id = i.clip_id
JOIN (SELECT clip_id, MAX(logged_at) AS t FROM ClipEvent WHERE stage = 'published' GROUP BY clip_id) p ON p.clip_id = i.clip_id
WHERE TIMESTAMPDIFF(SECOND, i.t, p.t) > 240
ORDER BY total_secs DESC, i.clip_id← Hotstar: Deduplicated Watch Minutes Across Overlapping Devices · Hotstar: Subscribers Covered on Every Playoff Match Day →