Each customer's first order

Step 1 Solve it yourself first

Tables you will use

orders customers

What it really tests

Picking exactly one row per group (the first event per user) with ROW_NUMBER, and joining back for extra columns. Used for activation, onboarding and funnel questions.

The pattern: First (or latest) row per group

"First purchase", "most recent login", "latest status", "earliest", one per user.

Picture it

flowchart LR
  O["Priya's orders by date<br/>101 · 102 · 103 · 105 · 109"] --> R["ROW_NUMBER<br/>1 · 2 · 3 · 4 · 5"]
  R --> K["keep 1:<br/>order 101 on 2024-01-06"]

The approach, step by step

1. Number each customer's orders by date: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id).
2. Add order_id as a tiebreaker in case two orders share a date.
3. Keep row number 1 in an outer query.
4. Join customers for the name and sign-up date, and work out the days between.

WITH numbered AS (
  SELECT customer_id, order_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
  FROM orders
)
SELECT c.name, n.order_id, n.order_date, n.amount,
       julianday(n.order_date) - julianday(c.signup_date) AS days_to_first_order
FROM numbered n
JOIN customers c ON c.customer_id = n.customer_id
WHERE n.rn = 1
ORDER BY c.customer_id;

SELECT c.name, o.order_id, o.order_date, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date = (
  SELECT MIN(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
)
ORDER BY c.customer_id;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — first purchase questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why ROW_NUMBER needs a tiebreaker here, with an example.

   Show answer

   ROW_NUMBER must give exactly one row number 1 per customer. If two orders share the earliest date, ordering by date alone leaves the tie unbroken, and the database may pick either one, and pick differently on another run or after an index changes. The answer looks fine but isn't repeatable.

   Example: if Priya had placed orders 101 and 111 both on 2024-01-06, ORDER BY order_date could return either as her first order. Adding order_id as a second sort key (ORDER BY order_date, order_id) makes 101 win every time.

2. Writefrom a blank page, then run it

   For each customer, show their latest order and its status.

   Show answer

   Number each customer's orders newest first, with order_id as the tiebreaker, and keep number 1. Zoe's latest (and only) order is still pending.

   sqlCopy

   WITH numbered AS (
     SELECT customer_id, order_id, order_date, status,
            ROW_NUMBER() OVER (
              PARTITION BY customer_id
              ORDER BY order_date DESC, order_id DESC
            ) AS rn
     FROM orders
   )
   SELECT c.name, n.order_id, n.order_date, n.status
   FROM numbered n
   JOIN customers c ON c.customer_id = n.customer_id
   WHERE n.rn = 1
   ORDER BY c.customer_id;

   Try it

   4 rows

   name	order_id	order_date	status
   Priya	109	2024-03-10	delivered
   Tom	108	2024-03-01	delivered
   Lena	107	2024-02-20	delivered
   Zoe	110	2024-03-16	pending

3. Predictsay it before you run it

   What would the solution return for Lena if the CTE only numbered delivered orders?

   Show answer

   Lena's first delivered order is 107 (20 February, £60), not the cancelled 106, so her days to first order become 21 instead of 13 (she signed up on 30 January).

   And Zoe disappears: her only order is pending, so she has no delivered orders to number.

   sqlCopy

   WITH numbered AS (
     SELECT customer_id, order_id, order_date, amount,
            ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
     FROM orders
     WHERE status = 'delivered'
   )
   SELECT c.name, n.order_id, n.order_date, n.amount,
          julianday(n.order_date) - julianday(c.signup_date) AS days_to_first_order
   FROM numbered n
   JOIN customers c ON c.customer_id = n.customer_id
   WHERE n.rn = 1
   ORDER BY c.customer_id;

   Try it

   3 rows

   name	order_id	order_date	amount	days_to_first_order
   Priya	101	2024-01-06	120	4
   Tom	104	2024-01-21	200	1
   Lena	107	2024-02-20	60	21

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Change the solution so customers with no orders appear with NULLs.

   Show answer

   Start from customers and LEFT JOIN the numbered orders. The n.rn = 1 test must go in the ON: in the WHERE, it would be NULL for customers with no orders and would drop them again. Omar and the second Tom now appear with NULLs.

   sqlCopy

   WITH numbered AS (
     SELECT customer_id, order_id, order_date, amount,
            ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
     FROM orders
   )
   SELECT c.name, n.order_id, n.order_date, n.amount,
          julianday(n.order_date) - julianday(c.signup_date) AS days_to_first_order
   FROM customers c
   LEFT JOIN numbered n ON n.customer_id = c.customer_id AND n.rn = 1
   ORDER BY c.customer_id;

   Try it

   6 rows

   name	order_id	order_date	amount	days_to_first_order
   Priya	101	2024-01-06	120	4
   Tom	104	2024-01-21	200	1
   Lena	106	2024-02-12	NULL	13
   Omar	NULL	NULL	NULL	NULL
   Zoe	110	2024-03-16	33	6
   Tom	NULL	NULL	NULL	NULL

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

For first-per-group I number rows with ROW_NUMBER partitioned by customer and ordered by date with the order id as a tiebreaker, keep row number one in an outer query, and join customers for the sign-up date to compute days to first order. I'd confirm whether cancelled orders count, and mention that a MIN-date join can return duplicates on ties while ROW_NUMBER always returns exactly one.

Follow-up questions

Tick each check when you can do it without notes