Coding questions · Mixpanel
Count Users Active After Signup
Count distinct users with at least one event on or after signup using a PostgreSQL query.
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.
Count Users with an Event On or After Signup
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 |
| pro | 2026-09-01 15:00:00 | 2 |
| enterprise | 2026-09-01 08:00:00 | 3 |
| free | NULL | 4 |
| pro | 2026-09-01 10:00:00 | 5 |
| trial | 2026-09-01 10:00:00 | 6 |
| free | 2026-09-01 13:00:00 | 7 |
| NULL | 2026-09-01 23:00:00 | 8 |
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-02 12:00:00 | 1 |
| 2026-09-02 12:00:00 | 1 |
| 2026-09-01 14:59:59 | 2 |
| 2027-01-01 00:00:00 | 3 |
| 2026-09-02 10:00:00 | 4 |
| NULL | 6 |
| 2026-09-01 12:59:59 | 7 |
| 2026-09-01 14:00:00 | 7 |
| 2026-09-01 23:00:00 | 8 |
| 2026-09-01 12:00:00 | 99 |
| 2026-09-01 12:00:00 | NULL |
Expected output
| users_with_event |
|---|
| 4 |