Broken silver: dedup keyed on user_id

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 20 minutes. Part of the Pro drill bank.

Ticket from the analytics team: 'Our events dashboard lost most of its data after the silver job was changed. dbt also still finds duplicate event ids in bronze.' You open the job and find that the cleanup keeps only 1 row per user, and not 1 row per event.

The silver job removes duplicates from ecommerce_events, but it does this by user_id instead of event_id. A user with 40 events ends up with one. Fix the query so that it keeps exactly 1 row for each event_id, and when the same event was received more than once, the row with the latest ingest_time wins. Return event_id, user_id, session_id, event_type, product_id, device, country, event_time, ordered by event_id. Keep ingest_time in the logic that picks the row, but do not return it. The query must give the same result if it runs again.

Requirements

  • Keep 1 row for every event_id.
  • When an event_id appears more than once, keep the one with the latest ingest_time.
  • Return the 8 columns in the order above, without ingest_time.
  • Order by event_id.

Constraints

  • The dedup key (the column you partition by) must be event_id.
  • ingest_time is used to choose the row but is not in the output.
  • Running the query twice on the same data must give the same rows.

Examples

Input: user_id | event_id | ingest_time U035 | E00011 | 2026-08-11 03:32:35 U035 | E00012 | 2026-08-11 03:40:12 U035 | E00011 | 2026-08-11 03:43:35 Output: event_id | user_id | session_id | event_type | product_id | device | country | event_time E00011 | U035 | S035-002 | purchase | SKU-008 | web | DE | 2026-08-11 03:32:35 E00012 | U035 | S035-002 | page_view | SKU-004 | ios | IN | 2026-08-11 03:40:12 Why this passes: Grouping by user_id keeps 1 row per user and drops E00012 entirely. Both E00011 and E00012 must survive at event grain.

Topics: debugging, dedup, clickstream.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Broken silver: dedup keyed on user_id

Production ticket: Silver clickstream collapsed to 1 row per user instead of 1 row per event_id.

The silver job deduplicates `ecommerce_events` but keys on `user_id` instead of `event_id`. Product analytics is missing most events; dbt still sees duplicate event_ids in bronze. Fix the shipped SQL so: - event_id, user_id, session_id, event_type, product_id, device, country, event_time - 1 row per event_id (latest ingest_time wins) - ordered by event_id - idempotent on replay Do not drop ingest_time from the dedup logic; drop it from the output only.