Coding questions · Meta
Write SQL and Python for data prep
This question evaluates data engineering competencies including SQL analytics with window functions and MERGE/INSERT patterns for idempotent incremental jobs, event-time handling (duplicates and late events), sessionization and retention metrics, plus Python-based JSON parsing, normalization, partitioned Parquet output and basic unit testing.
Given clickstream events (user_id, event_type, ts, properties) and a users table (user_id, signup_ts, plan), write SQL to compute DAU/WAU/MAU, D1/W1 retention cohorts, and sessionized metrics; implement an incremental daily job that updates aggregates idempotently using window functions and MERGE/INSERT patterns; and diagnose/handle duplicates and late events. Then, using Python (no heavy frameworks), implement a data-cleaning script that parses semi-structured JSON in the properties column, normalizes nested fields, and writes partitioned Parquet outputs with basic unit tests.
Compute DAU, WAU, and MAU from clickstream events
You are given a clickstream_events table with user-level events and a users table with signup information. Using these tables, write a SQL query to compute, for each calendar day from 2025-05-18 to 2025-05-24 (inclusive), the number of distinct active users for: (1) that day (DAU), (2) the trailing 7-day window ending on that day (WAU), and (3) the trailing 30-day window ending on that day (MAU). A user is considered active on a day if they have at least one event whose ts falls on that calendar date. Return one row per activity_date with columns activity_date, dau, wau_7d, and mau_30d.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_ts | TIMESTAMP | NOT NULL |
| plan | VARCHAR(20) | NOT NULL |
Sample data:
| plan | signup_ts | user_id |
|---|---|---|
| free | 2025-05-01 10:00:00 | 1 |
| pro | 2025-05-02 12:00:00 | 2 |
| free | 2025-05-08 09:30:00 | 3 |
| pro | 2025-05-15 15:45:00 | 4 |
| free | 2025-05-20 11:15:00 | 5 |
Table clickstream_events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| ingested_at | TIMESTAMP | NOT NULL |
| properties | VARCHAR(1000) |
Sample data:
| event_id | event_type | ingested_at | properties | ts | user_id |
|---|---|---|---|---|---|
| 1 | page_view | 2025-05-01 10:06:00 | {} | 2025-05-01 10:05:00 | 1 |
| 2 | login | 2025-05-18 09:00:05 | {} | 2025-05-18 09:00:00 | 1 |
| 3 | page_view | 2025-05-18 09:05:10 | {} | 2025-05-18 09:05:00 | 1 |
| 4 | click | 2025-05-19 10:00:05 | {} | 2025-05-19 10:00:00 | 1 |
| 5 | page_view | 2025-05-22 11:00:03 | {} | 2025-05-22 11:00:00 | 1 |
| 6 | signup_complete | 2025-05-02 12:30:10 | {} | 2025-05-02 12:30:00 | 2 |
| 7 | page_view | 2025-05-18 14:00:06 | {} | 2025-05-18 14:00:00 | 2 |
| 8 | page_view | 2025-05-18 14:01:00 | {} | 2025-05-18 14:00:00 | 2 |
| 9 | click | 2025-05-20 15:00:04 | {} | 2025-05-20 15:00:00 | 2 |
| 10 | page_view | 2025-05-23 16:00:01 | {} | 2025-05-23 16:00:00 | 2 |
| 11 | page_view | 2025-05-08 09:45:03 | {} | 2025-05-08 09:45:00 | 3 |
| 12 | click | 2025-05-21 13:00:02 | {} | 2025-05-21 13:00:00 | 3 |
| 13 | page_view | 2025-05-21 13:30:05 | {} | 2025-05-21 13:30:00 | 3 |
| 14 | page_view | 2025-05-15 16:00:02 | {} | 2025-05-15 16:00:00 | 4 |
| 15 | click | 2025-05-19 17:00:02 | {} | 2025-05-19 17:00:00 | 4 |
| 16 | login | 2025-05-24 18:00:01 | {} | 2025-05-24 18:00:00 | 4 |
| 17 | page_view | 2025-05-20 11:30:02 | {} | 2025-05-20 11:30:00 | 5 |
| 18 | click | 2025-05-20 11:45:02 | {} | 2025-05-20 11:45:00 | 5 |
| 19 | page_view | 2025-05-25 12:00:01 | {} | 2025-05-25 12:00:00 | 5 |
| 20 | click | 2025-05-20 08:00:00 | {} | 2025-05-18 09:02:00 | 1 |
| 21 | page_view | 2025-05-03 09:00:03 | {} | 2025-05-03 09:00:00 | 2 |
| 22 | page_view | 2025-05-21 10:00:03 | {} | 2025-05-21 10:00:00 | 5 |
Expected output
One row per calendar day from 2025-05-18 to 2025-05-24 with distinct active user counts for that day (dau), the trailing 7-day window (wau_7d), and the trailing 30-day window (mau_30d).
| activity_date | dau | mau_30d | wau_7d |
|---|---|---|---|
| 2025-05-18 | 2 | 4 | 3 |
| 2025-05-19 | 2 | 4 | 3 |
| 2025-05-20 | 2 | 5 | 4 |
| 2025-05-21 | 2 | 5 | 5 |
| 2025-05-22 | 1 | 5 | 5 |
| 2025-05-23 | 1 | 5 | 5 |
| 2025-05-24 | 1 | 5 | 5 |
Hints
- Derive a calendar date from ts and work at the (user_id, activity_date) grain first.
- Use conditional COUNT(DISTINCT ...) over date ranges to compute DAU/WAU/MAU.
Compute D1 and W1 retention cohorts
Using the users and clickstream_events tables, compute Day-1 (D1) and Week-1 (W1) retention for signup cohorts in May 2025. Define cohort_date as CAST(signup_ts AS DATE). For each cohort_date in 2025-05-01 through 2025-05-31, return: (1) the number of users who signed up on that date (signups), (2) the number who had at least one event on cohort_date + 1 day (d1_retained_users), (3) the D1 retention rate (d1_retained_users / signups), (4) the number who had at least one event between cohort_date + 1 day and cohort_date + 7 days inclusive (w1_retained_users), and (5) the W1 retention rate (w1_retained_users / signups). Return one row per cohort_date.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_ts | TIMESTAMP | NOT NULL |
| plan | VARCHAR(20) | NOT NULL |
Sample data:
| plan | signup_ts | user_id |
|---|---|---|
| free | 2025-05-01 10:00:00 | 1 |
| pro | 2025-05-02 12:00:00 | 2 |
| free | 2025-05-08 09:30:00 | 3 |
| pro | 2025-05-15 15:45:00 | 4 |
| free | 2025-05-20 11:15:00 | 5 |
Table clickstream_events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| ingested_at | TIMESTAMP | NOT NULL |
| properties | VARCHAR(1000) |
Sample data:
| event_id | event_type | ingested_at | properties | ts | user_id |
|---|---|---|---|---|---|
| 1 | page_view | 2025-05-01 10:06:00 | {} | 2025-05-01 10:05:00 | 1 |
| 2 | login | 2025-05-18 09:00:05 | {} | 2025-05-18 09:00:00 | 1 |
| 3 | page_view | 2025-05-18 09:05:10 | {} | 2025-05-18 09:05:00 | 1 |
| 4 | click | 2025-05-19 10:00:05 | {} | 2025-05-19 10:00:00 | 1 |
| 5 | page_view | 2025-05-22 11:00:03 | {} | 2025-05-22 11:00:00 | 1 |
| 6 | signup_complete | 2025-05-02 12:30:10 | {} | 2025-05-02 12:30:00 | 2 |
| 7 | page_view | 2025-05-18 14:00:06 | {} | 2025-05-18 14:00:00 | 2 |
| 8 | page_view | 2025-05-18 14:01:00 | {} | 2025-05-18 14:00:00 | 2 |
| 9 | click | 2025-05-20 15:00:04 | {} | 2025-05-20 15:00:00 | 2 |
| 10 | page_view | 2025-05-23 16:00:01 | {} | 2025-05-23 16:00:00 | 2 |
| 11 | page_view | 2025-05-08 09:45:03 | {} | 2025-05-08 09:45:00 | 3 |
| 12 | click | 2025-05-21 13:00:02 | {} | 2025-05-21 13:00:00 | 3 |
| 13 | page_view | 2025-05-21 13:30:05 | {} | 2025-05-21 13:30:00 | 3 |
| 14 | page_view | 2025-05-15 16:00:02 | {} | 2025-05-15 16:00:00 | 4 |
| 15 | click | 2025-05-19 17:00:02 | {} | 2025-05-19 17:00:00 | 4 |
| 16 | login | 2025-05-24 18:00:01 | {} | 2025-05-24 18:00:00 | 4 |
| 17 | page_view | 2025-05-20 11:30:02 | {} | 2025-05-20 11:30:00 | 5 |
| 18 | click | 2025-05-20 11:45:02 | {} | 2025-05-20 11:45:00 | 5 |
| 19 | page_view | 2025-05-25 12:00:01 | {} | 2025-05-25 12:00:00 | 5 |
| 20 | click | 2025-05-20 08:00:00 | {} | 2025-05-18 09:02:00 | 1 |
| 21 | page_view | 2025-05-03 09:00:03 | {} | 2025-05-03 09:00:00 | 2 |
| 22 | page_view | 2025-05-21 10:00:03 | {} | 2025-05-21 10:00:00 | 5 |
Expected output
One row per signup cohort_date in May 2025 with counts and rates for D1 and W1 retention.
| cohort_date | d1_retained_users | d1_retention_rate | signups | w1_retained_users | w1_retention_rate |
|---|---|---|---|---|---|
| 2025-05-01 | 0 | 0 | 1 | 0 | 0 |
| 2025-05-02 | 1 | 1 | 1 | 1 | 1 |
| 2025-05-08 | 0 | 0 | 1 | 0 | 0 |
| 2025-05-15 | 0 | 0 | 1 | 1 | 1 |
| 2025-05-20 | 1 | 1 | 1 | 1 | 1 |
Hints
- First map each user to a cohort_date based on signup_ts.
- Join cohorts to events and use conditional COUNT(DISTINCT ...) over date ranges for D1 and W1.
Sessionize clickstream events with a 30-minute inactivity threshold
Using the clickstream_events table, define user sessions over the inclusive date range from 2025-05-18 through 2025-05-21. Consider only events whose ts falls on one of those dates.
For each user_id, sort events by ts. Start a new session when the gap from the previous event for that user is strictly greater than 30 minutes; otherwise keep the event in the same session. Session IDs should start at 1 for each user and increase chronologically.
Return one row per session with:
user_idsession_idsession_startformatted asYYYY-MM-DD HH24:MI:SSsession_endformatted asYYYY-MM-DD HH24:MI:SSevent_countsession_duration_minutesas the integer minute difference between session end and start
Order the output by user_id, then session_id.
Table clickstream_events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| ingested_at | TIMESTAMP | NOT NULL |
| properties | VARCHAR(1000) |
Sample data:
| event_id | event_type | ingested_at | properties | ts | user_id |
|---|---|---|---|---|---|
| 1 | page_view | 2025-05-01 10:06:00 | {} | 2025-05-01 10:05:00 | 1 |
| 2 | login | 2025-05-18 09:00:05 | {} | 2025-05-18 09:00:00 | 1 |
| 20 | click | 2025-05-20 08:00:00 | {} | 2025-05-18 09:02:00 | 1 |
| 3 | page_view | 2025-05-18 09:05:10 | {} | 2025-05-18 09:05:00 | 1 |
| 4 | click | 2025-05-19 10:00:05 | {} | 2025-05-19 10:00:00 | 1 |
| 6 | signup_complete | 2025-05-02 12:30:10 | {} | 2025-05-02 12:30:00 | 2 |
| 7 | page_view | 2025-05-18 14:00:06 | {} | 2025-05-18 14:00:00 | 2 |
| 8 | page_view | 2025-05-18 14:01:00 | {} | 2025-05-18 14:00:00 | 2 |
| 9 | click | 2025-05-20 15:00:04 | {} | 2025-05-20 15:00:00 | 2 |
| 11 | page_view | 2025-05-08 09:45:03 | {} | 2025-05-08 09:45:00 | 3 |
| 12 | click | 2025-05-21 13:00:02 | {} | 2025-05-21 13:00:00 | 3 |
| 13 | page_view | 2025-05-21 13:30:05 | {} | 2025-05-21 13:30:00 | 3 |
| 14 | page_view | 2025-05-15 16:00:02 | {} | 2025-05-15 16:00:00 | 4 |
| 15 | click | 2025-05-19 17:00:02 | {} | 2025-05-19 17:00:00 | 4 |
| 17 | page_view | 2025-05-20 11:30:02 | {} | 2025-05-20 11:30:00 | 5 |
| 18 | click | 2025-05-20 11:45:02 | {} | 2025-05-20 11:45:00 | 5 |
| 22 | page_view | 2025-05-21 10:00:03 | {} | 2025-05-21 10:00:00 | 5 |
Expected output
One row per inferred session between 2025-05-18 and 2025-05-21, per user, with start/end timestamps, event counts, and durations in minutes. Session IDs restart from 1 for each user.
| event_count | session_duration_minutes | session_end | session_id | session_start | user_id |
|---|---|---|---|---|---|
| 3 | 5 | 2025-05-18 09:05:00 | 1 | 2025-05-18 09:00:00 | 1 |
| 1 | 0 | 2025-05-19 10:00:00 | 2 | 2025-05-19 10:00:00 | 1 |
| 2 | 0 | 2025-05-18 14:00:00 | 1 | 2025-05-18 14:00:00 | 2 |
| 1 | 0 | 2025-05-20 15:00:00 | 2 | 2025-05-20 15:00:00 | 2 |
| 2 | 30 | 2025-05-21 13:30:00 | 1 | 2025-05-21 13:00:00 | 3 |
| 1 | 0 | 2025-05-19 17:00:00 | 1 | 2025-05-19 17:00:00 | 4 |
| 2 | 15 | 2025-05-20 11:45:00 | 1 | 2025-05-20 11:30:00 | 5 |
| 1 | 0 | 2025-05-21 10:00:00 | 2 | 2025-05-21 10:00:00 | 5 |
Hints
- Use
LAG(ts)over each user's chronological events to detect inactivity gaps. - A running
SUMof the new-session flag is a compact way to assign session IDs per user. - Use
TO_CHARfor timestamp display andEXTRACT(EPOCH FROM interval) / 60for duration in minutes.
Incremental, idempotent DAU aggregation with deduplication and late-event handling
You maintain a daily DAU aggregate table, daily_user_activity, derived from raw clickstream events in clickstream_events. The raw feed has two data-quality issues you must handle:
- Duplicates: the same logical event can be ingested more than once. A duplicate is any group sharing the same
(user_id, event_type, ts); the rows differ only inevent_id,ingested_at, and possiblyproperties. Keep only the row with the earliestingested_atin each such group, and discard the rest. - Late events: an event's
ingested_atcan land on a day later than its activity dayts(e.g.event_id = 20hastson 2025-05-18 butingested_aton 2025-05-20). Because of this you must recompute affected days from scratch rather than trust the previously stored DAU.
Assume today is 2025-06-01. Your incremental job recomputes DAU for activity_date in the inclusive window 2025-05-18 through 2025-05-21 using all events currently available, then upserts the recomputed values into daily_user_activity (idempotently: rows in that window are overwritten, any date in the window not yet present would be inserted). DAU for a day is the number of distinct user_ids whose deduplicated ts falls on that calendar day.
Write a single PostgreSQL SELECT that returns the full contents of daily_user_activity as it would look immediately after this upsert runs. Specifically:
- For each
activity_datein 2025-05-18 .. 2025-05-21, return the freshly recomputeddauand alast_updated_atof2025-06-01 00:00:00(the recompute timestamp). - Any
daily_user_activityrow whoseactivity_dateis outside the recompute window must be passed through unchanged (in this dataset there are none, but your query must not drop them).
Return exactly the columns activity_date, dau, last_updated_at, one row per activity_date, sorted by activity_date ascending.
Table clickstream_events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| ingested_at | TIMESTAMP | NOT NULL |
| properties | VARCHAR(1000) |
Sample data:
| event_id | event_type | ingested_at | properties | ts | user_id |
|---|---|---|---|---|---|
| 1 | page_view | 2025-05-01 10:06:00 | {} | 2025-05-01 10:05:00 | 1 |
| 2 | login | 2025-05-18 09:00:05 | {} | 2025-05-18 09:00:00 | 1 |
| 3 | page_view | 2025-05-18 09:05:10 | {} | 2025-05-18 09:05:00 | 1 |
| 4 | click | 2025-05-19 10:00:05 | {} | 2025-05-19 10:00:00 | 1 |
| 5 | page_view | 2025-05-22 11:00:03 | {} | 2025-05-22 11:00:00 | 1 |
| 6 | signup_complete | 2025-05-02 12:30:10 | {} | 2025-05-02 12:30:00 | 2 |
| 7 | page_view | 2025-05-18 14:00:06 | {} | 2025-05-18 14:00:00 | 2 |
| 8 | page_view | 2025-05-18 14:01:00 | {} | 2025-05-18 14:00:00 | 2 |
| 9 | click | 2025-05-20 15:00:04 | {} | 2025-05-20 15:00:00 | 2 |
| 10 | page_view | 2025-05-23 16:00:01 | {} | 2025-05-23 16:00:00 | 2 |
| 11 | page_view | 2025-05-08 09:45:03 | {} | 2025-05-08 09:45:00 | 3 |
| 12 | click | 2025-05-21 13:00:02 | {} | 2025-05-21 13:00:00 | 3 |
| 13 | page_view | 2025-05-21 13:30:05 | {} | 2025-05-21 13:30:00 | 3 |
| 14 | page_view | 2025-05-15 16:00:02 | {} | 2025-05-15 16:00:00 | 4 |
| 15 | click | 2025-05-19 17:00:02 | {} | 2025-05-19 17:00:00 | 4 |
| 16 | login | 2025-05-24 18:00:01 | {} | 2025-05-24 18:00:00 | 4 |
| 17 | page_view | 2025-05-20 11:30:02 | {} | 2025-05-20 11:30:00 | 5 |
| 18 | click | 2025-05-20 11:45:02 | {} | 2025-05-20 11:45:00 | 5 |
| 19 | page_view | 2025-05-25 12:00:01 | {} | 2025-05-25 12:00:00 | 5 |
| 20 | click | 2025-05-20 08:00:00 | {} | 2025-05-18 09:02:00 | 1 |
| 21 | page_view | 2025-05-03 09:00:03 | {} | 2025-05-03 09:00:00 | 2 |
| 22 | page_view | 2025-05-21 10:00:03 | {} | 2025-05-21 10:00:00 | 5 |
Table daily_user_activity
| column | type | constraints |
|---|---|---|
| activity_date | DATE | PRIMARY KEY |
| dau | INT | NOT NULL |
| last_updated_at | TIMESTAMP | NOT NULL |
Sample data:
| activity_date | dau | last_updated_at |
|---|---|---|
| 2025-05-18 | 1 | 2025-05-19 00:00:00 |
| 2025-05-19 | 2 | 2025-05-20 00:00:00 |
| 2025-05-20 | 1 | 2025-05-21 00:00:00 |
| 2025-05-21 | 1 | 2025-05-22 00:00:00 |
Expected output
Final contents of daily_user_activity after the 2025-06-01 idempotent recompute of activity_date 2025-05-18..2025-05-21: deduplicated DAU per day with last_updated_at set to 2025-06-01 00:00:00, ordered by activity_date.
| activity_date | dau | last_updated_at |
|---|---|---|
| 2025-05-18 | 2 | 2025-06-01T00:00:00 |
| 2025-05-19 | 2 | 2025-06-01T00:00:00 |
| 2025-05-20 | 2 | 2025-06-01T00:00:00 |
| 2025-05-21 | 2 | 2025-06-01T00:00:00 |
Hints
- Deduplicate first: ROW_NUMBER() OVER (PARTITION BY user_id, event_type, ts ORDER BY ingested_at) and keep rn = 1 to retain the earliest-ingested copy.
- Bucket events by the activity day CAST(ts AS DATE) — not ingested_at — so late-arriving events land on the correct day; then COUNT(DISTINCT user_id) per day.
- Model the upsert without MERGE: UNION ALL the recomputed window rows with the pre-existing rows whose date is outside the window, then ORDER BY activity_date.