Prepaid Recharge Next-Month Retention by Cohort

Hard
519.

Prepaid Recharge Next-Month Retention by Cohort

Growth tracks how many new prepaid subscribers come back. A subscriber's cohort is the calendar month of their first recharge; they are retained if they recharge again at any time in the calendar month right after that first month (a second recharge later in the same month, or only two months later, does not count).

Return one row per cohort with the columns cohort_month (as 'YYYY-MM'), cohort_size, retained and retention_pct (100 * retained / cohort_size, rounded to 2 decimals). Order by cohort_month.

Tables

Table: Recharge

ColumnType
recharge_idint
subscriber_idint
recharge_datedate
amountint

Primary key: recharge_id.

Every recharge ever made; a subscriber's earliest row is their first recharge.

Example

The rows must come back in the order shown.

Recharge

recharge_idsubscriber_idrecharge_dateamount
112024-01-05299
212024-02-10299
322024-01-31579
422024-03-01579
532024-01-2098
632024-01-2898
742024-02-29299
842024-03-31299
952024-02-15719
1062023-12-30299
1 more row

Output

cohort_monthcohort_sizeretainedretention_pct
2023-1211100
2024-013133.33
2024-022150
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.

Prepaid Recharge Next-Month Retention by Cohort

Growth tracks how many new prepaid subscribers come back. A subscriber's cohort is the calendar month of their first recharge; they are retained if they…

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

Problem statement

Growth tracks how many new prepaid subscribers come back. A subscriber's cohort is the calendar month of their first recharge; they are retained if they recharge again at any time in the calendar month right after that first month (a second recharge later in the same month, or only two months later, does not count).

Return one row per cohort with the columns cohort_month (as 'YYYY-MM'), cohort_size, retained and retention_pct (100 * retained / cohort_size, rounded to 2 decimals). Order by cohort_month.

Tables

Table: Recharge

ColumnType
recharge_idint
subscriber_idint
recharge_datedate
amountint

Primary key: recharge_id.

Every recharge ever made; a subscriber's earliest row is their first recharge.

Examples

Example 1

Recharge

recharge_idsubscriber_idrecharge_dateamount
112024-01-05299
212024-02-10299
322024-01-31579
422024-03-01579
532024-01-2098
632024-01-2898
742024-02-29299
842024-03-31299
952024-02-15719
1062023-12-30299
1162024-01-02299

Output

cohort_monthcohort_sizeretainedretention_pct
2023-1211100
2024-013133.33
2024-022150

How to solve Prepaid Recharge Next-Month Retention by Cohort

Retention cohorts are built in three steps. First, each subscriber's first recharge: MIN(recharge_date) grouped by subscriber, whose month is the cohort. Second, a yes/no per subscriber: did they recharge in the month after? Third, aggregate per cohort.

The month arithmetic is where it goes wrong. Adding one month to 31 January is not well defined, so the reference first snaps the date to the first of its month (DATE_FORMAT(first_date, '%Y-%m-01')) and adds a month to that — 1 January becomes 1 February, 1 December 2023 becomes 1 January 2024. An EXISTS then asks whether the subscriber has any recharge whose 'YYYY-MM' equals that next month. A second recharge in the same month or one two months later does not match, which is exactly the statement's rule. The flags are summed for retained, and the percentage is rounded to two decimals.

The alternative numbers months as YEAR * 12 + MONTH, so the next month is the first month plus one, and finds retained subscribers with a LEFT JOIN against the distinct months each subscriber was active; COUNT(n.subscriber_id) counts only the matches. It rebuilds the 'YYYY-MM' label from the month number. Both read the table a couple of times.

Reference solution (MySQL)

WITH firsts AS (
  SELECT subscriber_id, MIN(recharge_date) AS first_date
  FROM Recharge
  GROUP BY subscriber_id
), cohort AS (
  SELECT subscriber_id,
         DATE_FORMAT(first_date, '%Y-%m') AS cohort_month,
         DATE_FORMAT(DATE_ADD(DATE_FORMAT(first_date, '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m') AS next_month
  FROM firsts
), flagged AS (
  SELECT c.cohort_month,
         CASE WHEN EXISTS (SELECT 1 FROM Recharge r
                           WHERE r.subscriber_id = c.subscriber_id
                             AND DATE_FORMAT(r.recharge_date, '%Y-%m') = c.next_month)
              THEN 1 ELSE 0 END AS kept
  FROM cohort c
)
SELECT cohort_month, COUNT(*) AS cohort_size, SUM(kept) AS retained,
       ROUND(100 * SUM(kept) / COUNT(*), 2) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_month

Another way

WITH m AS (
  SELECT DISTINCT subscriber_id, YEAR(recharge_date) * 12 + MONTH(recharge_date) AS mi FROM Recharge
), firsts AS (
  SELECT subscriber_id, MIN(mi) AS first_mi FROM m GROUP BY subscriber_id
)
SELECT CONCAT(FLOOR((f.first_mi - 1) / 12), '-', LPAD((f.first_mi - 1) % 12 + 1, 2, '0')) AS cohort_month,
       COUNT(*) AS cohort_size,
       COUNT(n.subscriber_id) AS retained,
       ROUND(100 * COUNT(n.subscriber_id) / COUNT(*), 2) AS retention_pct
FROM firsts f
LEFT JOIN m n ON n.subscriber_id = f.subscriber_id AND n.mi = f.first_mi + 1
GROUP BY f.first_mi
ORDER BY f.first_mi

← Median Completed Call Duration per Tower · Daily Network Drop Rate Including Silent Days →