Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Incremental clickstream dedup

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

The bronze clickstream table ecommerce_events is append-only. Replay traffic writes the same event_id again with a later ingest_time. Build a silver table with one row per event_id, keeping the row with the latest ingest_time. Return: event_id, user_id, session_id, event_type, product_id, device, country, event_time ordered by event_id Do not include ingest_time in the output.

Requirements

  • Output has exactly one row per distinct event_id, ordered by event_id.
  • One row per event_id; duplicates from late ingest disappear.

Constraints

  • A given event_id may appear more than once in ecommerce_events, always with the same event_time.
  • Return exactly these columns: event_id, user_id, session_id, event_type, product_id, device, country, event_time.
  • ecommerce_events.event_type: 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

beginner

Incremental clickstream dedup

Production ticket: Keep the latest ingest of each event_id from a messy clickstream.

The bronze clickstream table `ecommerce_events` is append-only. Replay traffic writes the same `event_id` again with a later `ingest_time`. Build a silver table with **one row per event_id**, keeping the row with the latest ingest_time. Return: - event_id, user_id, session_id, event_type, product_id, device, country, event_time - ordered by event_id Do not include ingest_time in the output.