Multi-Period Cohort Retention Analysis
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a Users table (with signup dates) and an Activity table (with activity dates). Using a recursive CTE to generate month offsets 0 through 3, compute for each signup cohort (by YYYY-MM) what percentage of users were active in each of the 4 months starting from their signup month. Offset 0 = the signup month itself, offset 1 = the month after, etc. Return (cohort_month, offset_month, cohort_size, active_users, retention_pct) ordered by cohort_month, then offset_month. retention_pct should be ROUND()ed to 1 decimal place. Table: Users
| Column Name | Type | Description |
|---|---|---|
| user_id | INT | Primary key |
| signup_date | DATE | Date user signed up |
Table: Activity
| Column Name | Type | Description |
|---|---|---|
| user_id | INT | References Users |
| activity_date | DATE | Date user was active |
Database Schema (Inferred)
Users
| Column Name | Example Value |
|---|---|
| user_id | 1 |
| signup_date | 2024-01-15 |
Activity
| Column Name | Example Value |
|---|---|
| user_id | 1 |
| activity_date | 2024-01-15 |
Example
Users
| user_id | signup_date |
|---|---|
| 1 | 2024-01-15 |
| 2 | 2024-01-20 |
| 3 | 2024-01-25 |
| 4 | 2024-02-05 |
| 5 | 2024-02-10 |
Activity
| user_id | activity_date |
|---|---|
| 1 | 2024-01-15 |
| 1 | 2024-02-10 |
| 1 | 2024-03-05 |
| 1 | 2024-04-12 |
| 2 | 2024-01-20 |
| 2 | 2024-02-18 |
| 2 | 2024-03-22 |
| 3 | 2024-01-25 |
| 3 | 2024-02-28 |
| 4 | 2024-02-05 |
| 4 | 2024-03-10 |
| 4 | 2024-04-15 |
| 5 | 2024-02-10 |
| 5 | 2024-03-20 |
Output
| cohort_month | offset_month | cohort_size | active_users | retention_pct |
|---|---|---|---|---|
| 2024-01 | 0 | 3 | 3 | 100 |
| 2024-01 | 1 | 3 | 3 | 100 |
| 2024-01 | 2 | 3 | 2 | 66.7 |
| 2024-01 | 3 | 3 | 1 | 33.3 |
| 2024-02 | 0 | 2 | 2 | 100 |
| 2024-02 | 1 | 2 | 2 | 100 |
| 2024-02 | 2 | 2 | 1 | 50 |
| 2024-02 | 3 | 2 | 0 | 0 |
Explanation:
Assign users to cohorts by STRFTIME('%Y-%m', signup_date). Generate offsets 0-3 with a recursive CTE. For each cohort+offset, count distinct users who have any activity row in the target month (cohort_month + offset months). Divide by cohort size.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Users
| user_id | signup_date |
|---|---|
| 1 | 2024-01-15 |
| 2 | 2024-01-20 |
| 3 | 2024-01-25 |
| 4 | 2024-02-05 |
| 5 | 2024-02-10 |
Activity
| user_id | activity_date |
|---|---|
| 1 | 2024-01-15 |
| 1 | 2024-02-10 |
| 1 | 2024-03-05 |
| 1 | 2024-04-12 |
| 2 | 2024-01-20 |
| 2 | 2024-02-18 |
| 2 | 2024-03-22 |
| 3 | 2024-01-25 |
| 3 | 2024-02-28 |
| 4 | 2024-02-05 |
| 4 | 2024-03-10 |
| 4 | 2024-04-15 |
| 5 | 2024-02-10 |
| 5 | 2024-03-20 |
Output
| cohort_month | offset_month | cohort_size | active_users | retention_pct |
|---|---|---|---|---|
| 2024-01 | 0 | 3 | 3 | 100 |
| 2024-01 | 1 | 3 | 3 | 100 |
| 2024-01 | 2 | 3 | 2 | 66.7 |
| 2024-01 | 3 | 3 | 1 | 33.3 |
| 2024-02 | 0 | 2 | 2 | 100 |
| 2024-02 | 1 | 2 | 2 | 100 |
| 2024-02 | 2 | 2 | 1 | 50 |
| 2024-02 | 3 | 2 | 0 | 0 |