Hotstar: Stage Times of Slow Highlights Clips

Hard
721.

Hotstar: Stage Times of Slow Highlights Clips

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

ColumnTypeValues
event_idint
clip_idint
match_idint
stageenumingested, cut, encoded, published
logged_atdatetime

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.

Example

The rows must come back in the order shown.

ClipEvent

event_idclip_idmatch_idstagelogged_at
117ingested2025-04-12 20:00:00
217ingested2025-04-12 20:00:20
317cut2025-04-12 20:01:10
417encoded2025-04-12 20:02:30
517encoded2025-04-12 20:04:00
617published2025-04-12 20:04:45
727ingested2025-04-12 20:10:00
827cut2025-04-12 20:10:50
927encoded2025-04-12 20:11:40
1027published2025-04-12 20:12:00
12 more rows

Output

clip_idmatch_idcut_secsencode_secspublish_secstotal_secs
3815090120360
177017045285
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: 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

ColumnType
event_idint
clip_idint
match_idint
stageenum(ingested, cut, encoded, published)
logged_atdatetime

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_idclip_idmatch_idstagelogged_at
117ingested2025-04-12 20:00:00
217ingested2025-04-12 20:00:20
317cut2025-04-12 20:01:10
417encoded2025-04-12 20:02:30
517encoded2025-04-12 20:04:00
617published2025-04-12 20:04:45
727ingested2025-04-12 20:10:00
827cut2025-04-12 20:10:50
927encoded2025-04-12 20:11:40
1027published2025-04-12 20:12:00
1138ingested2025-04-12 20:15:00
1238cut2025-04-12 20:16:00
1338cut2025-04-12 20:17:30
1438encoded2025-04-12 20:19:00
1538published2025-04-12 20:21:00
1648ingested2025-04-12 20:22:00
1748cut2025-04-12 20:23:00
1848encoded2025-04-12 20:24:00
1958ingested2025-04-12 20:30:00
2058cut2025-04-12 20:31:00
2158encoded2025-04-12 20:33:00
2258published2025-04-12 20:34:00

Output

clip_idmatch_idcut_secsencode_secspublish_secstotal_secs
3815090120360
177017045285

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_id

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