Monthly Signup Cohort Retention

Hard
418.

Monthly Signup Cohort Retention

The retention chart groups users into cohorts by signup month and asks what share of each cohort came back in the calendar month right after signup (month 1) and the month after that (month 2). Activity in the signup month itself does not count, and a user active several times in a month counts once.

Return one row per cohort with cohort_month ('YYYY-MM'), cohort_size, month_1_pct and month_2_pct — each 100 × retained users ÷ cohort_size, rounded to 1 decimal (0.0 when nobody returned). Order by cohort_month.

Tables

Table: users

ColumnType
user_idint
signed_up_ondate

Primary key: user_id.

One row per signup.

Table: activity

ColumnType
activity_idint
user_idint
active_ondate

Primary key: activity_id.

One row per day a user was active (several rows may share a month); user_id always names a row of users.

Example

The rows must come back in the order shown.

users

user_idsigned_up_on
12024-01-10
22024-01-25
32024-01-31
42024-02-05
52024-02-20
62024-03-02

activity

activity_iduser_idactive_on
112024-01-12
212024-02-03
312024-02-20
422024-03-15
532024-02-29
642024-03-10
752024-04-01
862024-03-05

Output

cohort_monthcohort_sizemonth_1_pctmonth_2_pct
2024-01366.733.3
2024-0225050
2024-03100
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.

Monthly Signup Cohort Retention — SQL Dates Problem

The retention chart groups users into cohorts by signup month and asks what share of each cohort came back in the calendar month right after signup (month…

  • Difficulty: Hard
  • Topics: Dates, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #418

Problem statement

The retention chart groups users into cohorts by signup month and asks what share of each cohort came back in the calendar month right after signup (month 1) and the month after that (month 2). Activity in the signup month itself does not count, and a user active several times in a month counts once.

Return one row per cohort with cohort_month ('YYYY-MM'), cohort_size, month_1_pct and month_2_pct — each 100 × retained users ÷ cohort_size, rounded to 1 decimal (0.0 when nobody returned). Order by cohort_month.

Tables

Table: users

ColumnType
user_idint
signed_up_ondate

Primary key: user_id.

One row per signup.

Table: activity

ColumnType
activity_idint
user_idint
active_ondate

Primary key: activity_id.

One row per day a user was active (several rows may share a month); user_id always names a row of users.

Examples

Example 1

users

user_idsigned_up_on
12024-01-10
22024-01-25
32024-01-31
42024-02-05
52024-02-20
62024-03-02

activity

activity_iduser_idactive_on
112024-01-12
212024-02-03
312024-02-20
422024-03-15
532024-02-29
642024-03-10
752024-04-01
862024-03-05

Output

cohort_monthcohort_sizemonth_1_pctmonth_2_pct
2024-01366.733.3
2024-0225050
2024-03100

How to solve Monthly Signup Cohort Retention

Retention is about calendar months relative to signup, so the cleanest representation is a month number: YEAR * 12 + MONTH. Month 1 for a cohort is then cohort key + 1, month 2 is + 2, and December rolls into January without special cases. Days do not matter — user 3 signed up on 31 January and was active on 29 February, which is month 1, even though only a few weeks passed.

Activity is reduced to distinct (user, month) pairs and LEFT JOINed to the cohorts, so a user who never returned still counts in cohort_size. Within each cohort, COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 1 THEN c.user_id END) counts the month-1 returners and the same with + 2 counts month 2; activity in the signup month matches neither. The shares are rounded to one decimal, which also keeps MySQL's four-decimal division out of the answer.

The alternative builds a 0/1 flag per user with EXISTS, comparing the activity's 'YYYY-MM' with that of DATE_ADD(signed_up_on, INTERVAL n MONTH) — adding months to 31 January lands on 29 February, still the right month. Averaging flags per cohort is the same ratio. The join version is one pass over each table; the EXISTS version is a lookup per user per month.

Reference solution (MySQL)

WITH cohorts AS (
  SELECT user_id, DATE_FORMAT(signed_up_on, '%Y-%m') AS cohort_month,
         YEAR(signed_up_on) * 12 + MONTH(signed_up_on) AS month_key
  FROM users
), active_months AS (
  SELECT DISTINCT user_id, YEAR(active_on) * 12 + MONTH(active_on) AS month_key
  FROM activity
)
SELECT c.cohort_month,
       COUNT(DISTINCT c.user_id) AS cohort_size,
       ROUND(100 * COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 1 THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS month_1_pct,
       ROUND(100 * COUNT(DISTINCT CASE WHEN a.month_key = c.month_key + 2 THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS month_2_pct
FROM cohorts c
LEFT JOIN active_months a ON a.user_id = c.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month

Another way

SELECT cohort_month, COUNT(*) AS cohort_size,
       ROUND(100 * SUM(m1) / COUNT(*), 1) AS month_1_pct,
       ROUND(100 * SUM(m2) / COUNT(*), 1) AS month_2_pct
FROM (
  SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
         CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id
              AND DATE_FORMAT(a.active_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 1 MONTH), '%Y-%m')) THEN 1 ELSE 0 END AS m1,
         CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id
              AND DATE_FORMAT(a.active_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 2 MONTH), '%Y-%m')) THEN 1 ELSE 0 END AS m2
  FROM users u
) flags
GROUP BY cohort_month
ORDER BY cohort_month

← Onboarding Funnel With Steps Taken in Order · Longest Daily Usage Streak per User →