Monthly Cohort Retention Analysis
Preview mode. Log in to edit, run, submit, and save progress.
Monthly Cohort Retention Analysis
You are given a UserActivity table recording which months each user was active. A user's cohort is defined as the first month they were ever active. Write a SQL query to compute, for each cohort month and each months_since_join offset (0, 1, 2, ...): the total number of users in that cohort (total_users), the number of those users who were still active at that offset (retained_users), and the retention percentage rounded to 1 decimal place. Return the result ordered by cohort_month, then months_since_join. Table: UserActivity
| Column Name | Type | Description |
|---|---|---|
| user_id | INT | ID of the user |
| activity_month | VARCHAR | Month the user was active (YYYY-MM) |
Examples
January cohort: users 1,2,3,5 (4 users). At month 0, all 4 active. At month 1, users 1 and 2 were active in Feb (50%). At month 2, users 1 and 5 were active in Mar (50%). February cohort: only user 4 joined in Feb. Active at +0 and +1 (Mar).
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.