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
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| device | varchar |
| started_at | datetime |
| ended_at | datetime |
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_id | profile_id | device | started_at | ended_at |
|---|---|---|---|---|
| 1 | 1 | tv | 2025-04-18 20:00:00 | 2025-04-18 21:00:00 |
| 2 | 1 | mobile | 2025-04-18 20:30:00 | 2025-04-18 20:45:00 |
| 3 | 1 | web | 2025-04-18 21:00:00 | 2025-04-18 21:30:00 |
| 4 | 1 | mobile | 2025-04-18 22:00:00 | 2025-04-18 22:10:00 |
| 5 | 2 | tv | 2025-04-18 19:00:00 | 2025-04-18 19:40:00 |
| 6 | 2 | mobile | 2025-04-18 19:20:00 | 2025-04-18 20:00:00 |
| 7 | 3 | tablet | 2025-04-18 18:00:00 | 2025-04-18 18:25:00 |
Output
| profile_id | logged_minutes | watch_minutes |
|---|---|---|
| 1 | 115 | 100 |
| 2 | 80 | 60 |
| 3 | 25 | 25 |
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_idAnother 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 →