First and Last Order Per Customer SQL Query

Problem statement

Prompt

Given the orders table below, write a query that returns, for each customer, the date of their first order and the date of their last order.

Output columns: customer_id, first_order_date, last_order_date, ordered by customer_id.
A customer with exactly one order has first_order_date = last_order_date.
When two orders share the same date, use the order id as the tiebreaker (lower id is "earlier").

Example

id	customer_id	order_date	amount
1	1	2024-01-10	50
2	1	2024-01-13	30
3	2	2024-02-01	90

Expected result:

customer_id	first_order_date	last_order_date
1	2024-01-10	2024-01-13
2	2024-02-01	2024-02-01

Constraints

orders.id is the primary key.
orders.order_date is not NULL.

Tips

FIRST_VALUE/LAST_VALUE need an explicit full frame (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — without it, LAST_VALUE only sees the current row.
A ROW_NUMBER ascending + descending pivot with MAX(CASE ...) also works and keeps one row per customer.

Schema

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    amount      INTEGER NOT NULL
);

Sample data

INSERT INTO orders (id, customer_id, order_date, amount) VALUES
  (1, 1, '2024-01-10', 50),
  (2, 1, '2024-01-13', 30),
  (3, 1, '2024-01-20', 20),
  (4, 2, '2024-02-01', 90),
  (5, 2, '2024-02-05', 40),
  (6, 3, '2024-03-01', 60);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: first/last order date per customer via row-number pivot.
WITH ranked AS (
    SELECT customer_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS rn_asc,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn_desc
    FROM orders
)
SELECT customer_id,
       MAX(CASE WHEN rn_asc = 1 THEN order_date END) AS first_order_date,
       MAX(CASE WHEN rn_desc = 1 THEN order_date END) AS last_order_date
FROM ranked
GROUP BY customer_id
ORDER BY customer_id;

Key concepts

FIRST_VALUE/LAST_VALUE
ROW_NUMBER
Pivot via MAX(CASE)
Tiebreakers

First and Last Order Per Customer