chaturIQ
Sign in
Thinking habit: Decide what a tie means

SQL: first order for each customer (with ties)

The question: Show each customer's first order: the earliest date, and if there are two that day, the lower order id.

orders

order_idcustomer_idorder_dateamount
1102026-03-01500
2102026-03-01200
3102026-03-04300
4112026-03-02150
5112026-03-03250

This query looks right. Run it, decide if it is, and fix it.

SELECT o.customer_id, o.order_id, o.amount
FROM orders o
JOIN (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders GROUP BY customer_id
) f ON f.customer_id = o.customer_id AND f.first_date = o.order_date;
Show the answer and why the first query is wrong

Why the usual answer is wrong

SELECT o.customer_id, o.order_id, o.amount
FROM orders o
JOIN (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders GROUP BY customer_id
) f ON f.customer_id = o.customer_id AND f.first_date = o.order_date;

Customer 10 placed orders 1 and 2 on the same first day, so both match the earliest date and the customer appears twice.

The answer

SELECT customer_id, order_id, amount
FROM (
  SELECT customer_id, order_id, amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
  FROM orders
)
WHERE rn = 1;

ROW_NUMBER gives exactly one number 1 per customer, and ordering by date then order id breaks the tie the way the question asks.

Use RANK instead if the question wants every order from the first day.

Practise more like this, on real business data

324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.

Open the lesson

More SQL interview questions