Rows remaining after duplicate cleanup

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

Duplicates in lb_orders need cleaning. In real work you would delete the extra rows, but this studio only grades SELECT queries, so you will show which rows would remain after the cleanup. A duplicate here means rows with the same (customer_id, product). For each such pair, keep only the earliest order, which is the row with the earliest order_date (and if two share a date, the lowest order_id). Return all the columns of lb_orders for the rows that remain, sorted by order_id. Do not write a DELETE.

Requirements

• Return 1 row for each (customer_id, product) pair.
• Keep the earliest order_date. If 2 rows have the same date, keep the lowest order_id.
• Return all columns of lb_orders.
• Sort by order_id.
• Write a SELECT, not a DELETE.

Constraints

• The table is lb_orders.
• Use ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY order_date, order_id) and keep rn = 1.
• Return only the 6 columns of the order table.

Examples

Input: lb_orders order_id | customer_id | product | product_category | amount | order_date 1 | C01 | Laptop | Electronics | 1200 | 2024-01-05 6 | C03 | Widget | Electronics | 50 | 2024-01-15 7 | C03 | Widget | Electronics | 55 | 2024-01-20 Output: order_id | customer_id | product | product_category | amount | order_date 1 | C01 | Laptop | Electronics | 1200 | 2024-01-05 6 | C03 | Widget | Electronics | 50 | 2024-01-15 Why this passes: Earliest order_date wins per (customer_id, product), so Widget keeps order_id 6.

Topics: lakebench, sql, row_number, delete-reframed.

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