Binge-Watch Runs of Three or More Episodes — SQL Problem
Take each profile's sessions in order of started_at. A session continues a run when it is the same series as the profile's previous session and starts no…
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #491
Problem statement
Take each profile's sessions in order of started_at. A session continues a run when it is the same series as the profile's previous session and starts no more than 15 minutes after that previous session's ended_at; otherwise it starts a new run. A run of at least 3 sessions is a binge.
Return every binge with the columns profile_id, series_name, binge_start (the started_at of its first session) and episodes (its number of sessions). Order by profile_id, then binge_start.
Tables
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| series_name | varchar |
| started_at | datetime |
| ended_at | datetime |
Primary key: session_id.
One row per episode watched. A profile's sessions never overlap, and times are whole minutes.
Examples
Example 1
WatchSession
| session_id | profile_id | series_name | started_at | ended_at |
|---|---|---|---|---|
| 1 | 1 | Coastal Cops | 2024-05-10 20:00:00 | 2024-05-10 20:45:00 |
| 2 | 1 | Coastal Cops | 2024-05-10 20:50:00 | 2024-05-10 21:35:00 |
| 3 | 1 | Coastal Cops | 2024-05-10 21:50:00 | 2024-05-10 22:35:00 |
| 4 | 1 | Coastal Cops | 2024-05-10 22:51:00 | 2024-05-10 23:36:00 |
| 5 | 2 | Backbenchers | 2024-05-11 13:00:00 | 2024-05-11 13:22:00 |
| 6 | 2 | Backbenchers | 2024-05-11 13:25:00 | 2024-05-11 13:47:00 |
| 7 | 2 | Hill Station | 2024-05-11 13:50:00 | 2024-05-11 14:30:00 |
| 8 | 2 | Backbenchers | 2024-05-11 14:31:00 | 2024-05-11 14:53:00 |
| 9 | 3 | Gully Kings | 2024-05-12 23:30:00 | 2024-05-12 23:58:00 |
| 10 | 3 | Gully Kings | 2024-05-13 00:05:00 | 2024-05-13 00:33:00 |
| 11 | 3 | Gully Kings | 2024-05-13 00:40:00 | 2024-05-13 01:08:00 |
Output
| profile_id | series_name | binge_start | episodes |
|---|---|---|---|
| 1 | Coastal Cops | 2024-05-10 20:00:00 | 3 |
| 3 | Gully Kings | 2024-05-12 23:30:00 | 3 |
How to solve Binge-Watch Runs of Three or More Episodes
This is sessionisation, a gaps-and-islands problem with a custom break rule. Order each profile's sessions by start time and look one row back with LAG: the previous session's series and end time. A session continues the run only when the series is the same and the gap from the previous end is at most 15 minutes; anything else — a different show in between, a longer break, or no previous row at all (LAG gives NULL, so the condition is not true) — starts a new run and gets flag 1.
A running SUM of the flags over the same ordering gives every session the number of the run it belongs to; grouping by (profile, run number) collapses each run to one row. COUNT(*) >= 3 keeps the binges, MIN(started_at) is the start, and because every session of a run shares the series, MIN(series_name) simply returns it while satisfying ONLY_FULL_GROUP_BY.
The edges in the example: profile 2 watched two Backbenchers episodes, switched to Hill Station, then came back — three episodes of one show, but not in a row, so no binge. Profile 1's fourth episode starts 16 minutes after the third ended, one minute too late, which leaves a run of exactly three. Profile 3's run crosses midnight, which the timestamp arithmetic handles. The alternative carries the run's first start forward with a running MAX instead of numbering runs. Both are one sort per profile.
Reference solution (MySQL)
WITH ordered AS (
SELECT profile_id, series_name, started_at,
LAG(series_name) OVER (PARTITION BY profile_id ORDER BY started_at) AS prev_series,
LAG(ended_at) OVER (PARTITION BY profile_id ORDER BY started_at) AS prev_end
FROM WatchSession
), flagged AS (
SELECT profile_id, series_name, started_at,
CASE WHEN prev_series = series_name
AND TIMESTAMPDIFF(MINUTE, prev_end, started_at) <= 15 THEN 0 ELSE 1 END AS new_run
FROM ordered
), runs AS (
SELECT profile_id, series_name, started_at,
SUM(new_run) OVER (PARTITION BY profile_id ORDER BY started_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_no
FROM flagged
)
SELECT profile_id, MIN(series_name) AS series_name, MIN(started_at) AS binge_start, COUNT(*) AS episodes
FROM runs
GROUP BY profile_id, run_no
HAVING COUNT(*) >= 3
ORDER BY profile_id, binge_startAnother way
WITH s AS (
SELECT profile_id, series_name, started_at,
CASE WHEN LAG(series_name) OVER w = series_name
AND started_at <= DATE_ADD(LAG(ended_at) OVER w, INTERVAL 15 MINUTE)
THEN NULL ELSE started_at END AS run_mark
FROM WatchSession
WINDOW w AS (PARTITION BY profile_id ORDER BY started_at)
), r AS (
SELECT profile_id, series_name,
MAX(run_mark) OVER (PARTITION BY profile_id ORDER BY started_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS binge_start
FROM s
)
SELECT profile_id, series_name, binge_start, COUNT(*) AS episodes
FROM r GROUP BY profile_id, series_name, binge_start HAVING COUNT(*) >= 3
ORDER BY profile_id, binge_start← Monthly Subscription Revenue With a Running Total · Longest Daily Listening Streak per Listener →