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
| Column | Type |
|---|---|
| recharge_id | int |
| subscriber_id | int |
| recharge_date | date |
| amount | int |
Primary key: recharge_id.
Every recharge ever made; a subscriber's earliest row is their first recharge.
Examples
Example 1
Recharge
| recharge_id | subscriber_id | recharge_date | amount |
|---|---|---|---|
| 1 | 1 | 2024-01-05 | 299 |
| 2 | 1 | 2024-02-10 | 299 |
| 3 | 2 | 2024-01-31 | 579 |
| 4 | 2 | 2024-03-01 | 579 |
| 5 | 3 | 2024-01-20 | 98 |
| 6 | 3 | 2024-01-28 | 98 |
| 7 | 4 | 2024-02-29 | 299 |
| 8 | 4 | 2024-03-31 | 299 |
| 9 | 5 | 2024-02-15 | 719 |
| 10 | 6 | 2023-12-30 | 299 |
| 11 | 6 | 2024-01-02 | 299 |
Output
| cohort_month | cohort_size | retained | retention_pct |
|---|---|---|---|
| 2023-12 | 1 | 1 | 100 |
| 2024-01 | 3 | 1 | 33.33 |
| 2024-02 | 2 | 1 | 50 |
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_monthAnother 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 →