Binge-Watch Runs of Three or More Episodes

Hard
491.

Binge-Watch Runs of Three or More Episodes

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

ColumnType
session_idint
profile_idint
series_namevarchar
started_atdatetime
ended_atdatetime

Primary key: session_id.

One row per episode watched. A profile's sessions never overlap, and times are whole minutes.

Example

The rows must come back in the order shown.

WatchSession

session_idprofile_idseries_namestarted_atended_at
11Coastal Cops2024-05-10 20:00:002024-05-10 20:45:00
21Coastal Cops2024-05-10 20:50:002024-05-10 21:35:00
31Coastal Cops2024-05-10 21:50:002024-05-10 22:35:00
41Coastal Cops2024-05-10 22:51:002024-05-10 23:36:00
52Backbenchers2024-05-11 13:00:002024-05-11 13:22:00
62Backbenchers2024-05-11 13:25:002024-05-11 13:47:00
72Hill Station2024-05-11 13:50:002024-05-11 14:30:00
82Backbenchers2024-05-11 14:31:002024-05-11 14:53:00
93Gully Kings2024-05-12 23:30:002024-05-12 23:58:00
103Gully Kings2024-05-13 00:05:002024-05-13 00:33:00
1 more row

Output

profile_idseries_namebinge_startepisodes
1Coastal Cops2024-05-10 20:00:003
3Gully Kings2024-05-12 23:30:003
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.

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

ColumnType
session_idint
profile_idint
series_namevarchar
started_atdatetime
ended_atdatetime

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_idprofile_idseries_namestarted_atended_at
11Coastal Cops2024-05-10 20:00:002024-05-10 20:45:00
21Coastal Cops2024-05-10 20:50:002024-05-10 21:35:00
31Coastal Cops2024-05-10 21:50:002024-05-10 22:35:00
41Coastal Cops2024-05-10 22:51:002024-05-10 23:36:00
52Backbenchers2024-05-11 13:00:002024-05-11 13:22:00
62Backbenchers2024-05-11 13:25:002024-05-11 13:47:00
72Hill Station2024-05-11 13:50:002024-05-11 14:30:00
82Backbenchers2024-05-11 14:31:002024-05-11 14:53:00
93Gully Kings2024-05-12 23:30:002024-05-12 23:58:00
103Gully Kings2024-05-13 00:05:002024-05-13 00:33:00
113Gully Kings2024-05-13 00:40:002024-05-13 01:08:00

Output

profile_idseries_namebinge_startepisodes
1Coastal Cops2024-05-10 20:00:003
3Gully Kings2024-05-12 23:30:003

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_start

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