Count Users Active After Signup
Company: Mixpanel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Count Users with an Event On or After Signup
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 who have at least one event whose `event_ts` is greater than or equal to their `signup_date`. Match events to user records by `user_id`.
A user with multiple qualifying events is counted once. Users with no qualifying events are excluded from the count.
Return exactly one row with one column named `users_with_event`. If nobody qualifies, the count is zero. Output row order is unrestricted.
Overview: Count distinct users with at least one event on or after signup using a PostgreSQL query.
Read the full Mixpanel Data Scientist interview experience this question came from
## Count Users with an Event On or After Signup
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 who have at least one event whose `event_ts` is greater than or equal to their `signup_date`. Match events to user records by `user_id`.
A user with multiple qualifying events is counted once. Users with no qualifying events are excluded from the count.
Return exactly one row with one column named `users_with_event`. If nobody qualifies, the count is zero. Output row order is unrestricted.
Tables
users(user_id INTEGER, signup_date TIMESTAMP, plan_type TEXT)
events(user_id INTEGER, event_ts TIMESTAMP)