Monthly Cohort Retention Analysis

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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)

Database Schema (Inferred)

UserActivity

Column Name	Example Value
user_id	1
activity_month	2023-01

Example

UserActivity

user_id	activity_month
1	2023-01
1	2023-02
1	2023-03
2	2023-01
2	2023-02
3	2023-01
4	2023-02
4	2023-03
5	2023-01
5	2023-03

Output

cohort_month	months_since_join	total_users	retained_users	retention_pct
2023-01	0	4	4	100
2023-01	1	4	2	50
2023-01	2	4	2	50
2023-02	0	1	1	100
2023-02	1	1	1	100

Explanation:

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.