Aggregate User Events and Check Join Uniqueness
Company: Mixpanel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Build a User Activity Summary from Event Data
You have two tables. The intended user summary contains one row per `user_id`, and the `users` table provides one record for each user.
| 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` | Identifier of the user associated with an event. |
| `events` | `event_ts` | Timestamp of the event. |
Write one PostgreSQL query that returns every user together with that user's event summary. Group all available event rows by `user_id` and compute:
- `event_cnt`: the number of non-NULL `event_ts` values.
- `active_days`: the number of distinct calendar dates represented by non-NULL `event_ts` values. Use the date component of the stored timestamp.
- `first_event`: the earliest non-NULL `event_ts` value.
Join the event summary to `users` so that users without any event rows are retained. For such users, the three event-summary fields are NULL; do not replace them with zero. Do not add a first-week restriction or any other time filter to the aggregation.
Return these columns in this order:
`user_id`, `signup_date`, `plan_type`, `event_cnt`, `active_days`, `first_event`.
The output row order is unrestricted. The query must not change either input table.
### Clarifications
The event summary has one row per `user_id`. The final result must retain the intended one-row-per-user invariant; the practical exercise also asks you to check that merging the summaries has not introduced duplicate user identifiers.
Overview: Aggregate user events and retain all users in PostgreSQL, computing event count, distinct active dates, earliest event, and join uniqueness.
Read the full Mixpanel Data Scientist interview experience this question came from
## Build a User Activity Summary from Event Data
You have two tables. The intended user summary contains one row per `user_id`, and the `users` table provides one record for each user.
| 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` | Identifier of the user associated with an event. |
| `events` | `event_ts` | Timestamp of the event. |
Write one PostgreSQL query that returns every user together with that user's event summary. Group all available event rows by `user_id` and compute:
- `event_cnt`: the number of non-NULL `event_ts` values.
- `active_days`: the number of distinct calendar dates represented by non-NULL `event_ts` values. Use the date component of the stored timestamp.
- `first_event`: the earliest non-NULL `event_ts` value.
Join the event summary to `users` so that users without any event rows are retained. For such users, the three event-summary fields are NULL; do not replace them with zero. Do not add a first-week restriction or any other time filter to the aggregation.
Return these columns in this order:
`user_id`, `signup_date`, `plan_type`, `event_cnt`, `active_days`, `first_event`.
The output row order is unrestricted. The query must not change either input table.
### Clarifications
The event summary has one row per `user_id`. The final result must retain the intended one-row-per-user invariant; the practical exercise also asks you to check that merging the summaries has not introduced duplicate user identifiers.
Tables
users(user_id INTEGER, signup_date TIMESTAMP WITHOUT TIME ZONE, plan_type TEXT)
events(user_id INTEGER, event_ts TIMESTAMP WITHOUT TIME ZONE)