Calculate First-Week Retention by Signup Cohort
Company: Mixpanel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Compute First-Week Retention by Signup Week
You have these tables:
| Table | Column | Meaning |
|---|---|---|
| `users` | `user_id` | User identifier. |
| `users` | `signup_date` | The user's signup date or timestamp. |
| `users` | `plan_type` | The user's plan type. |
| `events` | `user_id` | User associated with an event. |
| `events` | `event_ts` | Event timestamp. |
Group users into cohorts by the calendar week containing `signup_date`. In PostgreSQL, a calendar week begins on Monday at midnight. Name that week-start value `signup_week`.
For this exercise, a user is retained if the user has at least one matching event in the half-open interval from `signup_date` through, but not including, `signup_date` plus seven days. An event exactly at signup qualifies; an event exactly seven days later does not. Match events to users by `user_id`.
Write one read-only PostgreSQL query that returns one row for each signup-week cohort present in `users`, with these columns in this order:
- `signup_week`: the cohort's week-start timestamp.
- `cohort_size`: the number of distinct user identifiers in that cohort.
- `retained_users`: the number of distinct users in that cohort meeting the first-week event condition.
- `retention_rate`: `retained_users` divided by `cohort_size`, rounded to four decimal places.
The denominator includes cohort users with no qualifying event. Multiple qualifying events must not count a retained user more than once. Include cohorts with zero retained users; do not add calendar weeks that have no users.
Order the result by `signup_week` ascending. If a NULL week-start group is present, place it last.
Overview: Compute first-week activity retention by signup-week cohort with all cohort users in the denominator and zero-retention cohorts included.
Read the full Mixpanel Data Scientist interview experience this question came from
## Compute First-Week Retention by Signup Week
You have these tables:
| Table | Column | Meaning |
|---|---|---|
| `users` | `user_id` | User identifier. |
| `users` | `signup_date` | The user's signup date or timestamp. |
| `users` | `plan_type` | The user's plan type. |
| `events` | `user_id` | User associated with an event. |
| `events` | `event_ts` | Event timestamp. |
Group users into cohorts by the calendar week containing `signup_date`. In PostgreSQL, a calendar week begins on Monday at midnight. Name that week-start value `signup_week`.
For this exercise, a user is retained if the user has at least one matching event in the half-open interval from `signup_date` through, but not including, `signup_date` plus seven days. An event exactly at signup qualifies; an event exactly seven days later does not. Match events to users by `user_id`.
Write one read-only PostgreSQL query that returns one row for each signup-week cohort present in `users`, with these columns in this order:
- `signup_week`: the cohort's week-start timestamp.
- `cohort_size`: the number of distinct user identifiers in that cohort.
- `retained_users`: the number of distinct users in that cohort meeting the first-week event condition.
- `retention_rate`: `retained_users` divided by `cohort_size`, rounded to four decimal places.
The denominator includes cohort users with no qualifying event. Multiple qualifying events must not count a retained user more than once. Include cohorts with zero retained users; do not add calendar weeks that have no users.
Order the result by `signup_week` ascending. If a NULL week-start group is present, place it last.
Tables
users(user_id INTEGER, signup_date TIMESTAMP, plan_type TEXT)
events(user_id INTEGER, event_ts TIMESTAMP)