SQL data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 12 minutes. Part of the Pro drill bank.
For duplicate (customer_id, product) groups in lb_orders, keep only the row with the lowest order_id. Return the kept rows (all columns: order_id, customer_id, product, product_category, amount, order_date). Also return every non-duplicate row (groups of size 1). Order by order_id. Use ROW_NUMBER in a CTE.
Input: lb_orders order_id | customer_id | product 1 | C01 | Laptop 6 | C03 | Widget 7 | C03 | Widget Output: order_id | customer_id | product 1 | C01 | Laptop 6 | C03 | Widget Why this passes: Each user's eligible order totals are aggregated; users with higher totals appear earlier in the ranked output.
Topics: lakebench, sql, row_number, cte.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Return the row to keep when deduping by lowest order_id per customer+product.
For duplicate `(customer_id, product)` groups in `lb_orders`, keep only the row with the lowest `order_id`. Return the kept rows (all columns: `order_id`, `customer_id`, `product`, `product_category`, `amount`, `order_date`). Also return every non-duplicate row (groups of size 1). Order by `order_id`. Use ROW_NUMBER in a CTE.