Hotstar: Commentary Feed Switches and Final Language per Viewer
Hotstar wants to learn which commentary feeds viewers abandon mid-match.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #719
Problem statement
Hotstar wants to learn which commentary feeds viewers abandon mid-match. Every time a viewer tunes in to a feed, a FeedEvent row is written; reconnecting to the same language writes another row but is not a switch. A viewer's events in a match are ordered by tuned_at, then event_id (two events can share a timestamp).
For every (match, viewer) with at least 2 switches — an event whose language differs from that viewer's previous event in the match — return match_id, viewer_id, switches and final_language (the language of their last event in the match). 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: FeedEvent
| Column | Type |
|---|---|
| event_id | int |
| match_id | int |
| viewer_id | int |
| language | varchar |
| tuned_at | datetime |
Primary key: event_id.
One row per time a viewer tuned in to a commentary feed during a match.
Examples
Example 1
FeedEvent
| event_id | match_id | viewer_id | language | tuned_at |
|---|---|---|---|---|
| 1 | 11 | 301 | Hindi | 2025-04-12 19:00:00 |
| 2 | 11 | 302 | English | 2025-04-12 19:05:00 |
| 3 | 11 | 303 | Tamil | 2025-04-12 19:10:00 |
| 4 | 11 | 301 | Hindi | 2025-04-12 19:20:00 |
| 5 | 11 | 302 | Hindi | 2025-04-12 19:30:00 |
| 6 | 11 | 301 | English | 2025-04-12 19:40:00 |
| 7 | 11 | 303 | Telugu | 2025-04-12 19:50:00 |
| 8 | 11 | 303 | Tamil | 2025-04-12 19:50:00 |
| 9 | 11 | 301 | Hindi | 2025-04-12 20:10:00 |
| 10 | 11 | 301 | Tamil | 2025-04-12 20:30:00 |
| 11 | 12 | 301 | English | 2025-04-13 19:00:00 |
| 12 | 12 | 301 | Hindi | 2025-04-13 19:10:00 |
| 13 | 12 | 301 | English | 2025-04-13 19:15:00 |
Output
| match_id | viewer_id | switches | final_language |
|---|---|---|---|
| 11 | 301 | 3 | Tamil |
| 11 | 303 | 2 | Tamil |
| 12 | 301 | 2 | English |
How to solve Hotstar: Commentary Feed Switches and Final Language per Viewer
A switch is defined by adjacent rows, which is what LAG is for. Partition by match and viewer — a viewer's evening on another match is a different story — order by tuned_at, event_id, and compare each event's language with LAG(language). A row counts as a switch when the previous language exists and differs; a reconnection to the same language (viewer 301's second Hindi row) is not one, and a viewer's first event has a NULL previous language and is not one either.
The final language needs the last event, which the same pass provides: ROW_NUMBER() over the partition ordered descending marks it with 1, and MAX(CASE WHEN from_end = 1 THEN language END) lifts it into the grouped row. The event_id tie-break is not decoration — viewer 303 has two events at 19:50, and only the stated order makes their sequence Tamil → Telugu → Tamil, two switches ending on Tamil.
Grouping then sums the switches per (match, viewer) and the outer filter keeps those with two or more. The alternative finds each event's predecessor with a correlated subquery ordered by the same keys, and the last language the same way; it is quadratic per viewer, while the window version sorts each partition once.
Reference solution (MySQL)
WITH ordered_events AS (
SELECT match_id, viewer_id, language,
LAG(language) OVER (PARTITION BY match_id, viewer_id ORDER BY tuned_at, event_id) AS prev_language,
ROW_NUMBER() OVER (PARTITION BY match_id, viewer_id ORDER BY tuned_at DESC, event_id DESC) AS from_end
FROM FeedEvent
)
SELECT match_id, viewer_id, switches, final_language
FROM (
SELECT match_id, viewer_id,
SUM(CASE WHEN prev_language IS NOT NULL AND prev_language <> language THEN 1 ELSE 0 END) AS switches,
MAX(CASE WHEN from_end = 1 THEN language END) AS final_language
FROM ordered_events
GROUP BY match_id, viewer_id
) v
WHERE switches >= 2Another way
WITH paired AS (
SELECT e.match_id, e.viewer_id, e.language,
(SELECT p.language FROM FeedEvent p
WHERE p.match_id = e.match_id AND p.viewer_id = e.viewer_id
AND (p.tuned_at < e.tuned_at OR (p.tuned_at = e.tuned_at AND p.event_id < e.event_id))
ORDER BY p.tuned_at DESC, p.event_id DESC LIMIT 1) AS prev_language
FROM FeedEvent e
),
counts AS (
SELECT match_id, viewer_id, SUM(IF(prev_language <> language, 1, 0)) AS switches
FROM paired GROUP BY match_id, viewer_id
)
SELECT c.match_id, c.viewer_id, c.switches,
(SELECT l.language FROM FeedEvent l
WHERE l.match_id = c.match_id AND l.viewer_id = c.viewer_id
ORDER BY l.tuned_at DESC, l.event_id DESC LIMIT 1) AS final_language
FROM counts c
WHERE c.switches >= 2← Hotstar: 90th-Percentile Video Start Time by CDN · Hotstar: Deduplicated Watch Minutes Across Overlapping Devices →