Next-Month Retention of Learner Sign-up Cohorts
The growth team of a learning platform groups learners into cohorts by sign-up month and measures how many come back in the calendar month right after the…
- Difficulty: Hard
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #142
Problem statement
The growth team of a learning platform groups learners into cohorts by sign-up month and measures how many come back in the calendar month right after the one they signed up in (for a January sign-up, any activity in February).
Return one row per cohort with the columns cohort_month ('YYYY-MM'), signups, retained (learners of the cohort with at least one activity in the following month) and retention_pct (retained over signups as a percentage, rounded to 2 decimal places). A cohort with no retained learners shows 0. Activity in the sign-up month itself or two or more months later does not count. Order the rows by cohort_month.
Tables
Table: Learner
| Column | Type |
|---|---|
| learner_id | int |
| signed_up_on | date |
| plan | enum(free, plus, pro) |
Primary key: learner_id.
One row per learner account.
Table: LearnerActivity
| Column | Type |
|---|---|
| activity_id | int |
| learner_id | int |
| active_on | date |
| action | enum(watched_video, attempted_quiz, posted_doubt) |
Primary key: activity_id.
One row per action; active_on is never before the learner's sign-up date.
Examples
Example 1
Learner
| learner_id | signed_up_on | plan |
|---|---|---|
| 1 | 2024-01-05 | free |
| 2 | 2024-01-31 | plus |
| 3 | 2024-01-20 | free |
| 4 | 2024-02-14 | pro |
| 5 | 2024-02-29 | free |
| 6 | 2024-12-10 | free |
LearnerActivity
| activity_id | learner_id | active_on | action |
|---|---|---|---|
| 1 | 1 | 2024-01-06 | watched_video |
| 2 | 1 | 2024-02-03 | attempted_quiz |
| 3 | 1 | 2024-02-20 | watched_video |
| 4 | 2 | 2024-02-01 | posted_doubt |
| 5 | 3 | 2024-01-25 | watched_video |
| 6 | 3 | 2024-03-02 | watched_video |
| 7 | 4 | 2024-03-31 | attempted_quiz |
| 8 | 6 | 2025-01-02 | watched_video |
Output
| cohort_month | signups | retained | retention_pct |
|---|---|---|---|
| 2024-01 | 3 | 2 | 66.67 |
| 2024-02 | 2 | 1 | 50 |
| 2024-12 | 1 | 1 | 100 |
How to solve Next-Month Retention of Learner Sign-up Cohorts
A cohort report has two levels. First decide, per learner, whether they were retained; then aggregate per cohort. Doing it in that order avoids double counting a learner with several actions next month.
The cohort is DATE_FORMAT(signed_up_on, '%Y-%m'). "The month after" is a calendar notion, so the cleanest test is on month indexes: YEAR(d) * 12 + MONTH(d) numbers months consecutively across year boundaries, and a learner came back when some activity's index is exactly one more than the sign-up's. That handles December → January and the end-of-month cases (a 31 January sign-up active on 1 February is retained) with no date arithmetic at all. Adding one month to the sign-up date itself is risky, because 31 January plus one month is not well-defined — the alternative adds it to the first of the month instead.
With a 0/1 flag per learner, COUNT(*) is the cohort size, SUM(flag) the retained learners, and the percentage is rounded for display. Cohorts nobody returned from still appear with 0, because the flag comes from Learner, not from the activity table. The LEFT JOIN variant counts distinct learners on both sides for the same result.
Reference solution (MySQL)
WITH flagged AS (
SELECT l.learner_id, DATE_FORMAT(l.signed_up_on, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM LearnerActivity a
WHERE a.learner_id = l.learner_id
AND (YEAR(a.active_on) * 12 + MONTH(a.active_on)) - (YEAR(l.signed_up_on) * 12 + MONTH(l.signed_up_on)) = 1
) THEN 1 ELSE 0 END AS came_back
FROM Learner l
)
SELECT cohort_month, COUNT(*) AS signups, SUM(came_back) AS retained,
ROUND(100 * SUM(came_back) / COUNT(*), 2) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
SELECT DATE_FORMAT(l.signed_up_on, '%Y-%m') AS cohort_month, COUNT(DISTINCT l.learner_id) AS signups, COUNT(DISTINCT a.learner_id) AS retained, ROUND(100 * COUNT(DISTINCT a.learner_id) / COUNT(DISTINCT l.learner_id), 2) AS retention_pct FROM Learner l LEFT JOIN LearnerActivity a ON a.learner_id = l.learner_id AND DATE_FORMAT(a.active_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(DATE_FORMAT(l.signed_up_on, '%Y-%m'), '-01'), INTERVAL 1 MONTH), '%Y-%m') GROUP BY DATE_FORMAT(l.signed_up_on, '%Y-%m') ORDER BY cohort_month← Longest Daily Learning Streak of Each Learner · Median CGPA of Each Branch →