Coding questions · Mixpanel
Compare First-Week Retention by Plan Type
Compare first-week retention by recorded plan type with distinct-user counts, inclusive denominators, and four-decimal rates.
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_usersdivided byusers, 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.
Compare First-Week Retention Across Plan Types
Table users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| signup_date | TIMESTAMP | |
| plan_type | TEXT |
Sample data:
| plan_type | signup_date | user_id |
|---|---|---|
| free | 2026-09-01 12:00:00 | 1 |
| free | 2026-09-01 12:00:00 | 2 |
| free | 2026-09-01 18:30:00 | 3 |
| pro | 2026-09-01 09:00:00 | 4 |
| pro | 2026-09-02 10:00:00 | 5 |
| pro | 2026-09-03 15:00:00 | 6 |
| enterprise | 2026-09-01 13:00:00 | 7 |
| trial | 2026-09-04 12:00:00 | 8 |
| NULL | 2026-09-05 23:00:00 | 9 |
| NULL | 2026-09-05 23:00:00 | 10 |
| enterprise | NULL | 11 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| event_ts | TIMESTAMP |
Sample data:
| event_ts | user_id |
|---|---|
| 2026-09-01 12:00:00 | 1 |
| 2026-09-03 12:00:00 | 1 |
| 2026-09-01 11:59:59 | 2 |
| 2026-09-08 12:00:00 | 2 |
| 2026-09-08 18:29:59 | 3 |
| 2026-09-07 12:00:00 | 4 |
| 2026-09-07 12:00:00 | 4 |
| NULL | 5 |
| 2026-09-10 15:00:00 | 6 |
| 2026-09-01 12:59:59 | 7 |
| 2026-09-08 13:00:00 | 7 |
| 2026-09-05 23:00:00 | 9 |
| 2026-09-03 13:00:00 | 11 |
| 2026-09-03 12:00:00 | 99 |
| 2026-09-03 12:00:00 | NULL |
Expected output
| plan_type | users | retained_users | retention_rate |
|---|---|---|---|
| enterprise | 2 | 0 | 0 |
| free | 3 | 2 | 0.6667 |
| pro | 3 | 1 | 0.3333 |
| trial | 1 | 0 | 0 |
| NULL | 2 | 1 | 0.5 |