Keep lowest order_id per duplicate pair

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

In lb_orders, the same customer sometimes buys the same product more than once. For a clean report you want 1 row for each (customer_id, product) pair: the earliest order, which is the one with the lowest order_id. Return the rows to keep, with all columns (order_id, customer_id, product, product_category, amount, order_date). Rows that are not duplicated must be returned too. Sort by order_id. This is a SELECT of the rows to keep, not a DELETE. Use ROW_NUMBER inside a CTE (a named temporary result).

Requirements

• Return 1 row for each (customer_id, product) pair.
• For a pair with several orders, keep the one with the lowest order_id.
• Return all 6 columns of the order table.
• Keep non-duplicated rows as well.
• Sort by order_id.

Constraints

• The table is lb_orders.
• Use ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY order_id) and keep row number 1.
• Do not delete anything. Write a SELECT.

Examples

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