BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What was each customer's first order?

What was each customer's first order? MIN(order_date) gives the date but not the order. Number each customer's orders by date instead, starting again for every customer, and keep number 1.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Number each group's rows, keep number 1

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

Result:

customer_idorder_idorder_date
C10112026-02-03
C10222026-02-05
C10342026-02-14
C10452026-02-20
C10572026-03-04
C106112026-02-22

Step by step

  1. Their earliest order.
    WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1
  2. No join.
    WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1
  3. WHERE rn = 1.
    WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1
  4. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date).
    WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1

Practice

Try each one in your head first, then open the answer.

1. What was each customer's first order? Show customer_id, order_id and order_date.
WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1
2. What was each customer's latest order? Show customer_id, order_id and order_date.
WITH numbered AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM numbered WHERE rn = 1
3. What was each store's first order? Show store, order_id and order_date.
WITH numbered AS (SELECT store, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY store ORDER BY order_date) AS rn FROM orders) SELECT store, order_id, order_date FROM numbered WHERE rn = 1
4. What did each customer buy first? Show customer_id and product.
WITH numbered AS (SELECT customer_id, product, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, product FROM numbered WHERE rn = 1
5. Which customers used a coupon on their first order? Show customer_id and coupon.
WITH numbered AS (SELECT customer_id, coupon, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders) SELECT customer_id, coupon FROM numbered WHERE rn = 1 AND coupon IS NOT NULL

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed