Incremental clickstream dedup

SQL data engineering interview problem. Difficulty: beginner. Pattern: Deduplication. About 15 minutes. Free to practice.

Your website sends every click, view and purchase as an 'event'. Sometimes the same event is sent twice (for example when a message is retried), so the raw table ecommerce_events ends up with duplicates. Data teams call this raw table the 'bronze' layer. The cleaned-up copy that analysts read is called 'silver', and you are building it now.

In ecommerce_events, each event has an event_id. When an event is sent again, a second row is written with the same event_id but a later ingest_time (the time the row was received). Create the clean version with exactly 1 row for each event_id. When an event appears more than once, keep the row with the latest ingest_time. Return these columns in this order: event_id, user_id, session_id, event_type, product_id, device, country and event_time. Do not return ingest_time. Sort the result by event_id.

Requirements

• Return exactly 1 row for every distinct event_id.
• For an event that was sent more than once, keep only the row with the latest ingest_time.
• Do not include ingest_time in the output. It is only used to choose which duplicate to keep.
• Sort the result by event_id.

Constraints

• The table is ecommerce_events. It only ever grows, and old rows are never changed or deleted.
• The same event_id can appear more than once, and the copies always have the same event_time.
• Return only these 8 columns, in this order: event_id, user_id, session_id, event_type, product_id, device, country, event_time.
• event_type is one of: page_view, add_to_cart, purchase, bounce.

Examples

Input: event_id | user_id | session_id | ingest_time E00011 | U035 | S035-002 | 2026-08-11 03:32:35 E00011 | U035 | S035-002 | 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 Why this passes: Both rows share event_id and event_time; only ingest_time differs (the later one is the replay). The row with the later ingest_time wins, and ingest_time itself is dropped from the output.

Topics: window functions, dedup, late ingest.

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