SQL data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 12 minutes. Part of the Pro drill bank.
events_dedup has duplicate rows on (user_id, event_type, event_time). Keep the row with the lowest event_id per that key using ROW_NUMBER. Return event_id, user_id, event_type, event_time. Order by event_id.
Input: events_dedup event_id | user_id | event_type | event_time E1 | U1 | click | 2024-01-01 10:00:00 E2 | U1 | click | 2024-01-01 10:00:00 E3 | U1 | view | 2024-01-01 10:05:00 E4 | U2 | click | 2024-01-01 11:00:00 E5 | U2 | click | 2024-01-01 11:00:00 E6 | U3 | purchase | 2024-01-01 12:00:00 Output: event_id | user_id | event_type | event_time E1 | U1 | click | 2024-01-01 10:00:00 E3 | U1 | view | 2024-01-01 10:05:00 E4 | U2 | click | 2024-01-01 11:00:00 E6 | U3 | purchase | 2024-01-01 12:00:00 Why this passes: E2 shares the click key with E1 and loses on event_id order. E5 loses to E4.
Topics: lakebench, sql, row_number, dedup.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Keep the first event_id for duplicate user/type/time rows.
`events_dedup` has duplicate rows on `(user_id, event_type, event_time)`. Keep the row with the lowest `event_id` per that key using ROW_NUMBER. Return `event_id`, `user_id`, `event_type`, `event_time`. Order by `event_id`.