Count Users Retained on Day Seven
Company: Mixpanel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Count Users Retained on Day Seven
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. |
Write one read-only PostgreSQL query to count distinct users with at least one event on the calendar date seven days after their signup calendar date. Match events to users by `user_id`.
Compare the date components of `event_ts` and `signup_date`: time of day does not change whether an event is on day seven. This asks about the seventh calendar day, rather than any event during the first seven days. Count each qualifying user once even if that user has several events on that date.
Return exactly one row with one column named `d7_retained_users`. If nobody qualifies, the count is zero. Output row order is unrestricted.
Overview: Count users active on the seventh calendar day after signup, without double-counting repeated events.
Read the full Mixpanel Data Scientist interview experience this question came from