Compute Cohort Retention Rate
Company: Intuit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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 month
- `months_since_signup` — integer from 0 to 6
- `cohort_size` — number of users in the cohort
- `retained_users` — number of distinct users active in that relative month
- `retention_rate` — `retained_users / cohort_size` as a decimal
Make sure each user is counted at most once per cohort-month and relative month.
Overview: 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.
Compute cohort-based monthly retention for k = 0..6 where cohorts are defined by the calendar month of signup_ts. A user is retained in month k if they have at least one activity in user_activity during the calendar month that is k months after their cohort month. Return cohort_month (first day of month), months_since_signup (0..6), cohort_size, retained_users, and retention_rate (retained_users / cohort_size).
Tables
users(user_id BIGINT, signup_ts TIMESTAMP)
user_activity(user_id BIGINT, activity_ts TIMESTAMP, event_name VARCHAR)
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.