Coding questions · Intuit
Compute Cohort Retention Rate
This question evaluates a data scientist's competency in cohort-based retention analysis, time-based data manipulation, and aggregation using Data Manipulation (SQL/Python) techniques.
You are given two tables:
users(user_id BIGINT, signup_ts TIMESTAMP)— one row per user.user_activity(user_id BIGINT, activity_ts TIMESTAMP, event_name VARCHAR)— zero or more activity rows per user.
Assume all timestamps are in UTC. Define a user's cohort as the calendar month of signup_ts. A user is retained in month k if they have at least one row in user_activity during the calendar month that is k months after their cohort month. Month 0 is the signup month.
Write a SQL query to compute cohort-based monthly retention for k = 0, 1, 2, 3, 4, 5, 6.
Return these columns:
cohort_month— first day of the signup monthmonths_since_signup— integer from 0 to 6cohort_size— number of users in the cohortretained_users— number of distinct users active in that relative monthretention_rate—retained_users / cohort_sizeas a decimal
Make sure each user is counted at most once per cohort-month and relative month.
Compute Cohort Retention Rate
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | |
| signup_ts | TIMESTAMP |
Sample data:
| signup_ts | user_id |
|---|---|
| 2024-01-05 10:00:00 | 1 |
| 2024-01-20 15:30:00 | 2 |
| 2024-02-10 08:00:00 | 3 |
| 2024-03-01 00:00:00 | 4 |
Table user_activity
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | |
| activity_ts | TIMESTAMP | |
| event_name | VARCHAR |
Sample data:
| activity_ts | event_name | user_id |
|---|---|---|
| 2024-01-10 09:00:00 | open | 1 |
| 2024-02-12 12:00:00 | open | 1 |
| 2024-04-01 10:00:00 | purchase | 1 |
| 2024-01-25 18:45:00 | open | 2 |
| 2024-03-05 11:20:00 | open | 2 |
| 2024-03-20 16:05:00 | purchase | 2 |
| 2024-02-28 14:10:00 | open | 3 |
| 2024-04-02 09:30:00 | open | 3 |
| 2024-03-15 07:00:00 | open | 4 |
| 2024-04-01 13:00:00 | open | 4 |
| 2024-09-05 08:00:00 | open | 4 |
Expected output
One row per (cohort_month, months_since_signup in 0..6). cohort_month is the first day of the signup month (UTC). cohort_size is the total users who signed up in that cohort. retained_users counts distinct users from that cohort with at least one activity in the month that is k months after the cohort month (users counted at most once per cohort-month and k). retention_rate is retained_users divided by cohort_size.
| cohort_month | cohort_size | months_since_signup | retained_users | retention_rate |
|---|---|---|---|---|
| 2024-01-01 | 2 | 0 | 2 | 1 |
| 2024-01-01 | 2 | 1 | 1 | 0.5 |
| 2024-01-01 | 2 | 2 | 1 | 0.5 |
| 2024-01-01 | 2 | 3 | 1 | 0.5 |
| 2024-01-01 | 2 | 4 | 0 | 0 |
| 2024-01-01 | 2 | 5 | 0 | 0 |
| 2024-01-01 | 2 | 6 | 0 | 0 |
| 2024-02-01 | 1 | 0 | 1 | 1 |
| 2024-02-01 | 1 | 1 | 0 | 0 |
| 2024-02-01 | 1 | 2 | 1 | 1 |
| 2024-02-01 | 1 | 3 | 0 | 0 |
| 2024-02-01 | 1 | 4 | 0 | 0 |
| 2024-02-01 | 1 | 5 | 0 | 0 |
| 2024-02-01 | 1 | 6 | 0 | 0 |
| 2024-03-01 | 1 | 0 | 1 | 1 |
| 2024-03-01 | 1 | 1 | 1 | 1 |
| 2024-03-01 | 1 | 2 | 0 | 0 |
| 2024-03-01 | 1 | 3 | 0 | 0 |
| 2024-03-01 | 1 | 4 | 0 | 0 |
| 2024-03-01 | 1 | 5 | 0 | 0 |
| 2024-03-01 | 1 | 6 | 1 | 1 |
Hints
- Derive cohort_month as the first day of the signup month using date_trunc('month', signup_ts).
- Generate months_since_signup values 0..6 with generate_series and cross join to all cohorts.
- Deduplicate user_activity to one row per user per activity month before joining to avoid double counting.
- Join on activity_month = cohort_month + k months; count distinct users per (cohort_month, k).