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
| Column | Type |
|---|---|
| account_id | int |
| signed_up_on | date |
Primary key: account_id.
One row per account.
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| account_id | int |
| started_at | datetime |
Primary key: session_id.
One row per playback session; account_id always names a row of Account.
Examples
Example 1
Account
| account_id | signed_up_on |
|---|---|
| 1 | 2024-01-03 |
| 2 | 2024-01-31 |
| 3 | 2024-01-15 |
| 4 | 2024-02-10 |
| 5 | 2024-02-28 |
| 6 | 2024-03-01 |
WatchSession
| session_id | account_id | started_at |
|---|---|---|
| 1 | 1 | 2024-01-03 20:00:00 |
| 2 | 1 | 2024-02-14 21:00:00 |
| 3 | 2 | 2024-02-01 00:30:00 |
| 4 | 2 | 2024-02-02 19:00:00 |
| 5 | 3 | 2024-03-05 18:00:00 |
| 6 | 4 | 2024-03-31 23:59:00 |
| 7 | 5 | 2024-02-29 10:00:00 |
| 8 | 6 | 2024-04-02 09:00:00 |
Output
| cohort_month | cohort_size | retained_pct |
|---|---|---|
| 2024-01 | 3 | 66.67 |
| 2024-02 | 2 | 50 |
| 2024-03 | 1 | 100 |
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_monthAnother 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 →