Compare First-Week Retention by Plan Type
Company: Mixpanel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Compare First-Week Retention Across Plan Types
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. |
For this exercise, a user is retained if the user has at least one matching event whose `event_ts` is greater than or equal to `signup_date` and strictly less than `signup_date` plus seven days. Match events to user records by `user_id`.
Write one read-only PostgreSQL query that reports first-week retention separately for each `plan_type` present in `users`. Return these columns in this order:
- `plan_type`: the plan group.
- `users`: the number of distinct user identifiers in that plan group.
- `retained_users`: the number of distinct users in that group meeting the first-week event condition.
- `retention_rate`: `retained_users` divided by `users`, rounded to four decimal places.
Include users with no qualifying events in the denominator, and count each retained user once even when several events qualify. Include plan groups with zero retained users. Group by the recorded plan value; do not collapse plan types or filter to a subset of plans.
The output row order is unrestricted.
Overview: Compare first-week retention by recorded plan type with distinct-user counts, inclusive denominators, and four-decimal rates.
Read the full Mixpanel Data Scientist interview experience this question came from
## Compare First-Week Retention Across Plan Types
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. |
For this exercise, a user is retained if the user has at least one matching event whose `event_ts` is greater than or equal to `signup_date` and strictly less than `signup_date` plus seven days. Match events to user records by `user_id`.
Write one read-only PostgreSQL query that reports first-week retention separately for each `plan_type` present in `users`. Return these columns in this order:
- `plan_type`: the plan group.
- `users`: the number of distinct user identifiers in that plan group.
- `retained_users`: the number of distinct users in that group meeting the first-week event condition.
- `retention_rate`: `retained_users` divided by `users`, rounded to four decimal places.
Include users with no qualifying events in the denominator, and count each retained user once even when several events qualify. Include plan groups with zero retained users. Group by the recorded plan value; do not collapse plan types or filter to a subset of plans.
The output row order is unrestricted.
Tables
users(user_id INTEGER, signup_date TIMESTAMP, plan_type TEXT)
events(user_id INTEGER, event_ts TIMESTAMP)