Coding questions · Mixpanel
Aggregate User Events and Check Join Uniqueness
Aggregate user events and retain all users in PostgreSQL, computing event count, distinct active dates, earliest event, and join uniqueness.
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-NULLevent_tsvalues.active_days: the number of distinct calendar dates represented by non-NULLevent_tsvalues. Use the date component of the stored timestamp.first_event: the earliest non-NULLevent_tsvalue.
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.
Build a User Activity Summary from Event Data
Table users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | UNIQUE |
| signup_date | TIMESTAMP WITHOUT TIME ZONE | |
| plan_type | TEXT |
Sample data:
| plan_type | signup_date | user_id |
|---|---|---|
| free | 2024-02-28 12:00:00 | 101 |
| paid | 2024-03-01 00:00:00 | 102 |
| free | 2024-03-04 09:00:00 | 103 |
| trial | 2024-03-01 08:30:00 | 104 |
| paid | 2024-12-31 18:00:00 | 105 |
| NULL | 2025-01-02 12:00:00 | 106 |
| enterprise | 2024-03-15 10:00:00 | 107 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| event_ts | TIMESTAMP WITHOUT TIME ZONE |
Sample data:
| event_ts | user_id |
|---|---|
| 2024-03-10 10:15:00 | 101 |
| 2024-03-01 23:59:59 | 102 |
| 2024-02-29 23:59:59 | 101 |
| NULL | 104 |
| 2025-01-01 00:00:00 | 105 |
| 2024-02-29 23:59:59 | 101 |
| 2023-08-01 07:15:00 | 107 |
| 2024-03-01 00:00:00 | 102 |
| 2024-02-28 11:59:59 | 101 |
| 2024-03-03 14:00:00 | 999 |
| 2024-03-01 00:00:00 | 101 |
| NULL | 104 |
| 2024-12-31 23:59:59 | 105 |
| 2024-03-01 08:01:02 | 102 |
| NULL | 101 |
| 2024-06-01 12:00:00 | NULL |
| NULL | 107 |
Expected output
| user_id | signup_date | plan_type | event_cnt | active_days | first_event |
|---|---|---|---|---|---|
| 101 | 2024-02-28 12:00:00 | free | 5 | 4 | 2024-02-28 11:59:59 |
| 102 | 2024-03-01 00:00:00 | paid | 3 | 1 | 2024-03-01 00:00:00 |
| 103 | 2024-03-04 09:00:00 | free | NULL | NULL | NULL |
| 104 | 2024-03-01 08:30:00 | trial | 0 | 0 | NULL |
| 105 | 2024-12-31 18:00:00 | paid | 2 | 2 | 2024-12-31 23:59:59 |
| 106 | 2025-01-02 12:00:00 | NULL | NULL | NULL | NULL |
| 107 | 2024-03-15 10:00:00 | enterprise | 1 | 1 | 2023-08-01 07:15:00 |