Fantasy App Signup Cohorts Retained Into the Next Month
The fantasy app groups users into cohorts by the calendar month they signed up in.
- Difficulty: Hard
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #470
Problem statement
The fantasy app groups users into cohorts by the calendar month they signed up in. A user is retained when they entered at least one contest in the calendar month right after their signup month (a user who signed up on 31 January is retained by any entry in February; entries in the signup month itself or two months later do not count).
Return one row per signup month with the columns cohort_month (YYYY-MM), cohort_size (users who signed up that month), retained_users and retention_pct = 100 * retained_users / cohort_size, rounded to 2 decimal places. Cohorts with nobody retained show 0. Order the rows by cohort_month.
Tables
Table: AppUser
| Column | Type |
|---|---|
| user_id | int |
| signed_up_on | date |
| city | varchar |
Primary key: user_id.
One row per registered user.
Table: ContestJoin
| Column | Type |
|---|---|
| join_id | int |
| user_id | int |
| joined_on | date |
| entry_fee | int |
Primary key: join_id.
One row per contest entry; user_id is always in AppUser and joined_on is never before that user's signup.
Examples
Example 1
AppUser
| user_id | signed_up_on | city |
|---|---|---|
| 1 | 2024-01-05 | Pune |
| 2 | 2024-01-20 | Mumbai |
| 3 | 2024-01-31 | Delhi |
| 4 | 2024-02-10 | Kochi |
| 5 | 2024-02-14 | Jaipur |
| 6 | 2024-03-02 | Chennai |
ContestJoin
| join_id | user_id | joined_on | entry_fee |
|---|---|---|---|
| 1 | 1 | 2024-01-06 | 49 |
| 2 | 1 | 2024-02-03 | 49 |
| 3 | 1 | 2024-02-17 | 99 |
| 4 | 2 | 2024-03-01 | 25 |
| 5 | 3 | 2024-02-01 | 49 |
| 6 | 4 | 2024-02-11 | 99 |
| 7 | 5 | 2024-03-29 | 25 |
| 8 | 6 | 2024-03-05 | 49 |
Output
| cohort_month | cohort_size | retained_users | retention_pct |
|---|---|---|---|
| 2024-01 | 3 | 2 | 66.67 |
| 2024-02 | 2 | 1 | 50 |
| 2024-03 | 1 | 0 | 0 |
How to solve Fantasy App Signup Cohorts Retained Into the Next Month
Retention is a per-user fact aggregated per cohort, so it pays to do it in two steps. First decide, for each user, whether some entry falls in the month after the signup month. Comparing month labels avoids day arithmetic: the target month is DATE_FORMAT(DATE_ADD(signed_up_on, INTERVAL 1 MONTH), '%Y-%m'). DATE_ADD by a month clamps 31 January to the end of February, so the label is still February — the month, not "30 days later", is what the statement asks for. DISTINCT (or EXISTS) makes a user with several entries count once.
Then group users by signup month. The LEFT JOIN to the retained list keeps every user, so COUNT(*) is the cohort size and COUNT(r.user_id) counts only the matched ones; a cohort where nobody came back gets 0 rather than disappearing. The percentage is rounded at the end.
The alternative computes a 0/1 flag per user with EXISTS over the next month's first and last day (LAST_DAY), which is the date-range version of the same idea and can use an index on joined_on. Each plan reads the users once plus one probe per user.
Reference solution (MySQL)
WITH retained AS (
SELECT DISTINCT u.user_id
FROM AppUser u
JOIN ContestJoin j ON j.user_id = u.user_id
WHERE DATE_FORMAT(j.joined_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 1 MONTH), '%Y-%m')
)
SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
COUNT(*) AS cohort_size,
COUNT(r.user_id) AS retained_users,
ROUND(100 * COUNT(r.user_id) / COUNT(*), 2) AS retention_pct
FROM AppUser u
LEFT JOIN retained r ON r.user_id = u.user_id
GROUP BY DATE_FORMAT(u.signed_up_on, '%Y-%m')
ORDER BY cohort_monthAnother way
SELECT cohort_month, COUNT(*) AS cohort_size, SUM(kept) AS retained_users, ROUND(100 * SUM(kept) / COUNT(*), 2) AS retention_pct
FROM (
SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM ContestJoin j
WHERE j.user_id = u.user_id
AND j.joined_on >= DATE_ADD(DATE_SUB(u.signed_up_on, INTERVAL DAY(u.signed_up_on) - 1 DAY), INTERVAL 1 MONTH)
AND j.joined_on <= LAST_DAY(DATE_ADD(DATE_SUB(u.signed_up_on, INTERVAL DAY(u.signed_up_on) - 1 DAY), INTERVAL 1 MONTH))
) THEN 1 ELSE 0 END AS kept
FROM AppUser u
) t
GROUP BY cohort_month
ORDER BY cohort_month← Median Fantasy Points per Gameweek by Player Role · Days Each Player Was Out Injured, Merging Overlapping Records →