Hotstar: Deduplicated Watch Minutes Across Overlapping Devices

Hard
720.

Hotstar: Deduplicated Watch Minutes Across Overlapping Devices

A profile often has the match open on the TV and the phone at once, so adding up session lengths overstates how long a person actually watched. Hotstar's engagement metric counts each minute of a profile's time once, however many devices were playing it.

For every profile, return profile_id, logged_minutes (the plain sum of its session lengths) and watch_minutes (the length of the union of its sessions — overlapping or touching sessions merge into one interval). Session length is TIMESTAMPDIFF(MINUTE, started_at, ended_at); all times fall on whole minutes. 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: ViewingSession

ColumnType
session_idint
profile_idint
devicevarchar
started_atdatetime
ended_atdatetime

Primary key: session_id.

One playback session on one device; ended_at is always after started_at. A session can run past midnight.

Example

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

ViewingSession

session_idprofile_iddevicestarted_atended_at
11tv2025-04-18 20:00:002025-04-18 21:00:00
21mobile2025-04-18 20:30:002025-04-18 20:45:00
31web2025-04-18 21:00:002025-04-18 21:30:00
41mobile2025-04-18 22:00:002025-04-18 22:10:00
52tv2025-04-18 19:00:002025-04-18 19:40:00
62mobile2025-04-18 19:20:002025-04-18 20:00:00
73tablet2025-04-18 18:00:002025-04-18 18:25:00

Output

profile_idlogged_minuteswatch_minutes
1115100
28060
32525
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: Deduplicated Watch Minutes Across Overlapping Devices

A profile often has the match open on the TV and the phone at once, so adding up session lengths overstates how long a person actually watched.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #720

Problem statement

A profile often has the match open on the TV and the phone at once, so adding up session lengths overstates how long a person actually watched. Hotstar's engagement metric counts each minute of a profile's time once, however many devices were playing it.

For every profile, return profile_id, logged_minutes (the plain sum of its session lengths) and watch_minutes (the length of the union of its sessions — overlapping or touching sessions merge into one interval). Session length is TIMESTAMPDIFF(MINUTE, started_at, ended_at); all times fall on whole minutes. 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: ViewingSession

ColumnType
session_idint
profile_idint
devicevarchar
started_atdatetime
ended_atdatetime

Primary key: session_id.

One playback session on one device; ended_at is always after started_at. A session can run past midnight.

Examples

Example 1

ViewingSession

session_idprofile_iddevicestarted_atended_at
11tv2025-04-18 20:00:002025-04-18 21:00:00
21mobile2025-04-18 20:30:002025-04-18 20:45:00
31web2025-04-18 21:00:002025-04-18 21:30:00
41mobile2025-04-18 22:00:002025-04-18 22:10:00
52tv2025-04-18 19:00:002025-04-18 19:40:00
62mobile2025-04-18 19:20:002025-04-18 20:00:00
73tablet2025-04-18 18:00:002025-04-18 18:25:00

Output

profile_idlogged_minuteswatch_minutes
1115100
28060
32525

How to solve Hotstar: Deduplicated Watch Minutes Across Overlapping Devices

This is interval merging, a gaps-and-islands problem on ranges. Sort each profile's sessions by start time. A session opens a new merged interval exactly when it starts after the latest end among all sessions before it; otherwise it overlaps or touches the current interval and extends it. Comparing with only the previous session is the classic bug: in profile 1, the phone session 20:30–20:45 ends before the web session starts at 21:00, but the TV session that started earlier runs to 21:00, so the web session still continues the same interval. Hence MAX(ended_at) over ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING.

A running SUM of the new-interval flags numbers the intervals; grouping by it gives each interval's MIN(started_at) and MAX(ended_at), and their lengths add up to the deduplicated minutes. The comparison is strict (started_at > prev_end), so touching sessions merge — which does not change the total but keeps the intervals honest. TIMESTAMPDIFF handles a session that crosses midnight.

The alternative never sorts: an interval's left end is a start time that no other session strictly before it covers, a right end is an end time no session continues past, and each left end pairs with the nearest right end after it. It is quadratic per profile; the window version is one sort per profile.

Reference solution (MySQL)

WITH prior AS (
  SELECT session_id, profile_id, started_at, ended_at,
         MAX(ended_at) OVER (PARTITION BY profile_id ORDER BY started_at, session_id
                             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end
  FROM ViewingSession
),
flagged AS (
  SELECT profile_id, started_at, ended_at,
         SUM(CASE WHEN prev_end IS NULL OR started_at > prev_end THEN 1 ELSE 0 END)
           OVER (PARTITION BY profile_id ORDER BY started_at, session_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
  FROM prior
),
islands AS (
  SELECT profile_id, MIN(started_at) AS s, MAX(ended_at) AS e
  FROM flagged
  GROUP BY profile_id, island
),
watched AS (
  SELECT profile_id, SUM(TIMESTAMPDIFF(MINUTE, s, e)) AS watch_minutes FROM islands GROUP BY profile_id
),
logged AS (
  SELECT profile_id, SUM(TIMESTAMPDIFF(MINUTE, started_at, ended_at)) AS logged_minutes
  FROM ViewingSession GROUP BY profile_id
)
SELECT l.profile_id, l.logged_minutes, w.watch_minutes
FROM logged l
JOIN watched w ON w.profile_id = l.profile_id

Another way

WITH starts AS (
  SELECT DISTINCT a.profile_id, a.started_at AS s
  FROM ViewingSession a
  WHERE NOT EXISTS (
    SELECT 1 FROM ViewingSession t
    WHERE t.profile_id = a.profile_id AND t.started_at < a.started_at AND t.ended_at >= a.started_at
  )
),
ends AS (
  SELECT DISTINCT a.profile_id, a.ended_at AS e
  FROM ViewingSession a
  WHERE NOT EXISTS (
    SELECT 1 FROM ViewingSession t
    WHERE t.profile_id = a.profile_id AND t.started_at <= a.ended_at AND t.ended_at > a.ended_at
  )
),
islands AS (
  SELECT s.profile_id, s.s, (SELECT MIN(e.e) FROM ends e WHERE e.profile_id = s.profile_id AND e.e > s.s) AS e
  FROM starts s
)
SELECT v.profile_id,
       SUM(TIMESTAMPDIFF(MINUTE, v.started_at, v.ended_at)) AS logged_minutes,
       (SELECT SUM(TIMESTAMPDIFF(MINUTE, i.s, i.e)) FROM islands i WHERE i.profile_id = v.profile_id) AS watch_minutes
FROM ViewingSession v
GROUP BY v.profile_id

← Hotstar: Commentary Feed Switches and Final Language per Viewer · Hotstar: Stage Times of Slow Highlights Clips →