SQL data engineering interview problem. Difficulty: advanced. Pattern: Deduplication. About 18 minutes. Part of the Pro drill bank.
Studio grades SELECT results, so do not write DELETE. Return the rows that would remain if you removed duplicate (customer_id, product) rows from lb_orders, keeping only the earliest order_date (break ties with lowest order_id). Columns: all lb_orders columns. Order by order_id. Return columns in this order: order_id, customer_id, product, product_category, amount, order_date.
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
Interview-style drill: SELECT the rows that would remain after keeping earliest order_date per customer+product.
Studio grades SELECT results, so do not write DELETE. Return the rows that would remain if you removed duplicate `(customer_id, product)` rows from `lb_orders`, keeping only the earliest `order_date` (break ties with lowest `order_id`). Columns: all `lb_orders` columns. Order by `order_id`.