Deduplicate events with ROW_NUMBER

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 12 minutes. Free to practice.

An events table has duplicate rows. The same user did the same thing at the same time, but it was recorded twice with different event_id values. You want to keep one of each. In events_dedup, duplicates are rows with the same user_id, event_type and event_time. For each such group, keep only the row with the lowest event_id, using ROW_NUMBER. Return event_id, user_id, event_type and event_time, sorted by event_id.

Requirements

• Treat rows with the same user_id, event_type and event_time as duplicates.
• Keep only the row with the lowest event_id in each duplicate group.
• Keep rows that have no duplicate.
• Return the 4 columns and sort by event_id.

Constraints

• The table is events_dedup.
• Use ROW_NUMBER() OVER (PARTITION BY user_id, event_type, event_time ORDER BY event_id) and keep rn = 1.
• Return only these 4 columns, in this order: event_id, user_id, event_type, event_time.

Examples

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