Hotstar: Commentary Feed Switches and Final Language per Viewer

Hard
719.

Hotstar: Commentary Feed Switches and Final Language per Viewer

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

ColumnType
event_idint
match_idint
viewer_idint
languagevarchar
tuned_atdatetime

Primary key: event_id.

One row per time a viewer tuned in to a commentary feed during a match.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

FeedEvent

event_idmatch_idviewer_idlanguagetuned_at
111301Hindi2025-04-12 19:00:00
211302English2025-04-12 19:05:00
311303Tamil2025-04-12 19:10:00
411301Hindi2025-04-12 19:20:00
511302Hindi2025-04-12 19:30:00
611301English2025-04-12 19:40:00
711303Telugu2025-04-12 19:50:00
811303Tamil2025-04-12 19:50:00
911301Hindi2025-04-12 20:10:00
1011301Tamil2025-04-12 20:30:00
3 more rows

Output

match_idviewer_idswitchesfinal_language
113013Tamil
113032Tamil
123012English
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: 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

ColumnType
event_idint
match_idint
viewer_idint
languagevarchar
tuned_atdatetime

Primary key: event_id.

One row per time a viewer tuned in to a commentary feed during a match.

Examples

Example 1

FeedEvent

event_idmatch_idviewer_idlanguagetuned_at
111301Hindi2025-04-12 19:00:00
211302English2025-04-12 19:05:00
311303Tamil2025-04-12 19:10:00
411301Hindi2025-04-12 19:20:00
511302Hindi2025-04-12 19:30:00
611301English2025-04-12 19:40:00
711303Telugu2025-04-12 19:50:00
811303Tamil2025-04-12 19:50:00
911301Hindi2025-04-12 20:10:00
1011301Tamil2025-04-12 20:30:00
1112301English2025-04-13 19:00:00
1212301Hindi2025-04-13 19:10:00
1312301English2025-04-13 19:15:00

Output

match_idviewer_idswitchesfinal_language
113013Tamil
113032Tamil
123012English

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 >= 2

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