Next-Month Viewing Retention of Streaming Sign-Up Cohorts

Hard
493.

Next-Month Viewing Retention of Streaming Sign-Up Cohorts

Accounts are grouped into cohorts by the calendar month they signed up in. An account is retained when it has at least one watch session in the calendar month right after its sign-up month (an account that signed up any day in January is retained if it watched anything from 1 to 29 February). Sessions in the sign-up month itself, or two months later, do not count.

Return one row per cohort with the columns cohort_month (as YYYY-MM), cohort_size and retained_pct (retained accounts as a percentage of cohort_size, rounded to 2 decimal places). Order by cohort_month.

Tables

Table: Account

ColumnType
account_idint
signed_up_ondate

Primary key: account_id.

One row per account.

Table: WatchSession

ColumnType
session_idint
account_idint
started_atdatetime

Primary key: session_id.

One row per playback session; account_id always names a row of Account.

Example

The rows must come back in the order shown.

Account

account_idsigned_up_on
12024-01-03
22024-01-31
32024-01-15
42024-02-10
52024-02-28
62024-03-01

WatchSession

session_idaccount_idstarted_at
112024-01-03 20:00:00
212024-02-14 21:00:00
322024-02-01 00:30:00
422024-02-02 19:00:00
532024-03-05 18:00:00
642024-03-31 23:59:00
752024-02-29 10:00:00
862024-04-02 09:00:00

Output

cohort_monthcohort_sizeretained_pct
2024-01366.67
2024-02250
2024-031100
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.

Next-Month Viewing Retention of Streaming Sign-Up Cohorts

Accounts are grouped into cohorts by the calendar month they signed up in.

  • Difficulty: Hard
  • Topics: Dates, Subqueries, Aggregation
  • Dialect: MySQL
  • Problem: #493

Problem statement

Accounts are grouped into cohorts by the calendar month they signed up in. An account is retained when it has at least one watch session in the calendar month right after its sign-up month (an account that signed up any day in January is retained if it watched anything from 1 to 29 February). Sessions in the sign-up month itself, or two months later, do not count.

Return one row per cohort with the columns cohort_month (as YYYY-MM), cohort_size and retained_pct (retained accounts as a percentage of cohort_size, rounded to 2 decimal places). Order by cohort_month.

Tables

Table: Account

ColumnType
account_idint
signed_up_ondate

Primary key: account_id.

One row per account.

Table: WatchSession

ColumnType
session_idint
account_idint
started_atdatetime

Primary key: session_id.

One row per playback session; account_id always names a row of Account.

Examples

Example 1

Account

account_idsigned_up_on
12024-01-03
22024-01-31
32024-01-15
42024-02-10
52024-02-28
62024-03-01

WatchSession

session_idaccount_idstarted_at
112024-01-03 20:00:00
212024-02-14 21:00:00
322024-02-01 00:30:00
422024-02-02 19:00:00
532024-03-05 18:00:00
642024-03-31 23:59:00
752024-02-29 10:00:00
862024-04-02 09:00:00

Output

cohort_monthcohort_sizeretained_pct
2024-01366.67
2024-02250
2024-031100

How to solve Next-Month Viewing Retention of Streaming Sign-Up Cohorts

Retention is a property of the account, so compute it per account first and aggregate second. Each account gets its cohort label, DATE_FORMAT(signed_up_on, '%Y-%m'), and the label of the month that follows. DATE_ADD(signed_up_on, INTERVAL 1 MONTH) clamps to the end of a shorter month (31 January → 29 February), so its YYYY-MM is always the next calendar month — adding 30 days instead would jump from 31 January into March.

Whether the account was retained is then a yes/no question: EXISTS a session whose month label equals the next-month label. Using EXISTS (or a DISTINCT list of retained accounts) keeps an account with several sessions from being counted several times; a plain join from accounts to sessions would inflate both the cohort size and the retained count.

Finally group by cohort: COUNT(*) is the size and 100 * SUM(flag) / COUNT(*) the rate, rounded. The alternative computes the month distance directly with TIMESTAMPDIFF(MONTH, …) on first-of-month dates. Sign-up-month and two-months-later sessions fall outside both tests. The cost is one probe into sessions per account with an index on account_id.

Reference solution (MySQL)

WITH cohort AS (
  SELECT account_id,
         DATE_FORMAT(signed_up_on, '%Y-%m') AS cohort_month,
         DATE_FORMAT(DATE_ADD(signed_up_on, INTERVAL 1 MONTH), '%Y-%m') AS next_month
  FROM Account
), flags AS (
  SELECT c.cohort_month,
         CASE WHEN EXISTS (SELECT 1 FROM WatchSession w
                           WHERE w.account_id = c.account_id
                             AND DATE_FORMAT(w.started_at, '%Y-%m') = c.next_month) THEN 1 ELSE 0 END AS retained
  FROM cohort c
)
SELECT cohort_month, COUNT(*) AS cohort_size, ROUND(100 * SUM(retained) / COUNT(*), 2) AS retained_pct
FROM flags
GROUP BY cohort_month
ORDER BY cohort_month

Another way

SELECT LEFT(a.signed_up_on, 7) AS cohort_month, COUNT(*) AS cohort_size,
       ROUND(100 * COUNT(r.account_id) / COUNT(*), 2) AS retained_pct
FROM Account a
LEFT JOIN (
  SELECT DISTINCT w.account_id FROM WatchSession w JOIN Account x ON x.account_id = w.account_id
  WHERE TIMESTAMPDIFF(MONTH, DATE_FORMAT(x.signed_up_on, '%Y-%m-01'), DATE_FORMAT(w.started_at, '%Y-%m-01')) = 1
) r ON r.account_id = a.account_id
GROUP BY LEFT(a.signed_up_on, 7)
ORDER BY cohort_month

← Longest Daily Listening Streak per Listener · Median Session Length by Device →