Next-Month Retention of Learner Sign-up Cohorts

Hard
142.

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

ColumnTypeValues
learner_idint
signed_up_ondate
planenumfree, plus, pro

Primary key: learner_id.

One row per learner account.

Table: LearnerActivity

ColumnTypeValues
activity_idint
learner_idint
active_ondate
actionenumwatched_video, attempted_quiz, posted_doubt

Primary key: activity_id.

One row per action; active_on is never before the learner's sign-up date.

Example

The rows must come back in the order shown.

Learner

learner_idsigned_up_onplan
12024-01-05free
22024-01-31plus
32024-01-20free
42024-02-14pro
52024-02-29free
62024-12-10free

LearnerActivity

activity_idlearner_idactive_onaction
112024-01-06watched_video
212024-02-03attempted_quiz
312024-02-20watched_video
422024-02-01posted_doubt
532024-01-25watched_video
632024-03-02watched_video
742024-03-31attempted_quiz
862025-01-02watched_video

Output

cohort_monthsignupsretainedretention_pct
2024-013266.67
2024-022150
2024-1211100
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 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

ColumnType
learner_idint
signed_up_ondate
planenum(free, plus, pro)

Primary key: learner_id.

One row per learner account.

Table: LearnerActivity

ColumnType
activity_idint
learner_idint
active_ondate
actionenum(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_idsigned_up_onplan
12024-01-05free
22024-01-31plus
32024-01-20free
42024-02-14pro
52024-02-29free
62024-12-10free

LearnerActivity

activity_idlearner_idactive_onaction
112024-01-06watched_video
212024-02-03attempted_quiz
312024-02-20watched_video
422024-02-01posted_doubt
532024-01-25watched_video
632024-03-02watched_video
742024-03-31attempted_quiz
862025-01-02watched_video

Output

cohort_monthsignupsretainedretention_pct
2024-013266.67
2024-022150
2024-1211100

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_month

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