Monthly Cohort Retention
CoupangData Scientist · Technical Screen
Last updated
This question tests the ability to transform raw user activity logs into a cohort retention matrix by calculating the time elapsed between a user's first interaction and subsequent activity.
Monthly Cohort Retention
Given a table user_activity containing records of user interactions, construct a monthly cohort retention table. The table should represent the percentage of users who returned to the platform in each subsequent month, grouped by their cohort (the month of their first activity).
Schema
Table: user_activity
user_id(INT)activity_date(DATE)
Goal
Write a SQL query that returns the following columns:
cohort_month: The first month the user appeared in the data.month_number: The distance in months from thecohort_month(e.g., 0 for the first month, 1 for one month later).retention_rate: The percentage of users from thecohort_monthwho were active in the currentmonth_number.
Examples
Input user_activity:
| user_id | activity_date |
|---|---|
| 1 | 2023-01-05 |
| 1 | 2023-02-10 |
| 2 | 2023-01-20 |
| 3 | 2023-02-01 |
Output:
| cohort_month | month_number | retention_rate |
|---|---|---|
| 2023-01 | 0 | 1.00 |
| 2023-01 | 1 | 0.50 |
| 2023-02 | 0 | 1.00 |
Constraints
- Assume
activity_dateranges over a 2-year period. - You must identify the
cohort_monthfor eachuser_idbefore calculating the month difference. - Treat the first month of activity as
month_number = 0.
Hints
Hint 1
First, create a subquery or CTE to determine the earliest activity date for every unique user_id.
Hint 2
Use date truncation functions to normalize all activity dates to the first day of their respective months.
Hint 3
Calculate the month difference using a function like DATEDIFF or by extracting year and month parts to determine the relative 'month_number'.
Follow-up questions
- How would you modify the query if you wanted to analyze rolling 30-day retention instead of calendar-month retention?
- If the dataset contained millions of rows, what indexes or partition strategies would you suggest for the user_activity table to speed up this aggregation?
- How would you visualize this retention matrix using a heatmap, and what specific trends would you look for to identify 'churn'?
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.