Each customer's latest order

SQL · Core
30 min read, about 1 h 30 min with practice

Problem
Difficulty: medium
Asked often: high
Reviewed 2026-11

New to some of the words above? Open a quick recap. Each takes about two minutes.

Quick recapJoins· 2 min

A join matches rows from two tables. JOIN ... ON keeps every pair of rows where the condition is true. A row that matches twice appears twice.

SELECT o.order_id, c.name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

Quick recapGROUP BY and MAX· 2 min

GROUP BY folds the rows of each group into one row. After that, you can only select the group’s key and aggregates such as COUNT, SUM or MAX.

SELECT customer_id, MAX(order_date) AS latest
FROM orders
GROUP BY customer_id;   -- one row per customer: the latest date, not the order

Quick recapSubqueries· 2 min

A subquery is a query inside another query, in brackets. It can stand in for a value, or for a whole table in FROM (then it needs a name).

SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);   -- a value

SELECT * FROM (SELECT customer_id FROM orders) AS t;   -- a table named t

Quick recapThe order a query runs in· 2 min

You write SELECT first, but the database runs it almost last. The order is:

FROM and JOIN: get the rows
WHERE: drop rows
GROUP BY and HAVING: fold groups, drop groups
Window functions, such as ROW_NUMBER() OVER (...)
SELECT: pick and name the columns
ORDER BY, then LIMIT

So WHERE can’t see a column made later, like an alias from SELECT or a window function’s result.

See the problem

Support wants a screen that shows, for every customer, the last thing they ordered. Here are some rows of the orders table:

order_id	customer_id	order_date	total
101	1	2026-09-02	29.89
102	2	2026-09-05	18.00
103	1	2026-09-20	104.00
104	3	2026-09-21	53.18
106	2	2026-10-03	69.00
108	1	2026-10-11	36.99
109	2	2026-10-14	13.19
110	2	2026-10-14	59.00

Find each customer’s latest order before reading on. Customers 1 and 3 are easy. Customer 2 ordered twice on 14 October. Which one is “the latest”?

Define the task

Input: the orders table (order_id, customer_id, order_date, status, total).
Output: one row per customer who has ordered: customer_id, order_id, order_date, total. Any row order is fine.
Rules: the latest order has the newest order_date. On a tie, keep the higher order_id (it was placed later). Customers with no orders don’t appear.

Example: in the rows above, customer 1 gets order 108, customer 2 gets 110 (the tie goes to the higher id), and customer 3 gets 104.

This is the top-N per group pattern, here with N = 1. Interviewers ask it in many forms: each employee’s latest salary, each user’s last login, the top 3 products per category.

Pause and think

Hints open one at a time:

Hint 1 of 3

Start smaller: how would you get just the latest date for each customer?

Hint 2 of 3

GROUP BY customer_id with MAX(order_date) gives that date, but not the order itself. How could you get the whole row back?

Hint 3 of 3

Imagine each customer's orders numbered 1, 2, 3, newest first. Which rows would you keep?

Understand the idea

Picture each customer’s receipts as a separate pile, newest on top. To answer the question, take the top receipt from every pile. For two receipts with the same date, agree on a rule first: the higher order number goes on top.

SQL can do exactly that. A window function looks at a group of rows without folding them into one, the way GROUP BY does. ROW_NUMBER() numbers the rows of each group (each PARTITION), in the order you choose.

Watch it run, step by step, in the order the database runs the query. The player stops to ask what happens next. You can also type your own orders below it. The picture leaves out total so it fits on a phone.

Number each customer's orders, keep the first

FROM orders
PARTITION BY
ORDER BY in each group
ROW_NUMBER
WHERE rn = 1
ORDER BY

orders
customer_id	order_id	order_date
1	101	2026-09-02
2	102	2026-09-05
1	103	2026-09-20
3	104	2026-09-21
2	106	2026-10-03
1	108	2026-10-11
2	109	2026-10-14
2	110	2026-10-14

Step 1 of 8. The inner query runs first. FROM orders reads every row: 8 orders.

Speed Ask me first Show as text

1SELECT customer_id, order_id, order_date, total
2FROM (
3  SELECT o.*,
4         ROW_NUMBER() OVER (
5           PARTITION BY customer_id
6           ORDER BY order_date DESC, order_id DESC
7         ) AS rn
8  FROM orders AS o
9) AS ranked
10WHERE rn = 1
11ORDER BY customer_id;

Climb the ladder

Three ways to solve it. Each one shows the idea, a walkthrough on the rows above, the query, its cost, and where it breaks. Here n is the number of orders.

The idea: Keep an order if its date equals the latest date of its customer, found by a subquery.

On the rows above: for order 101, the subquery reads all 8 orders and finds customer 1’s latest date, 2026-10-11. 101 isn’t on that date, so it’s dropped. Then the same again for 102, and for every other row: 8 + 8 × 8 = 72 rows read.

A subquery for every row

FROM orders
WHERE, with a subquery per row
ORDER BY

orders
customer_id	order_id	order_date
1	101	2026-09-02
2	102	2026-09-05
1	103	2026-09-20
3	104	2026-09-21
2	106	2026-10-03
1	108	2026-10-11
2	109	2026-10-14
2	110	2026-10-14

Rows read so far: 8

Step 1 of 10. FROM orders reads every row: 8 orders. Now WHERE checks them one at a time.

Speed Ask me first Show as text

1SELECT o.customer_id, o.order_id, o.order_date, o.total
2FROM orders AS o
3WHERE o.order_date = (
4  SELECT MAX(o2.order_date)
5  FROM orders AS o2
6  WHERE o2.customer_id = o.customer_id
7)
8ORDER BY o.customer_id, o.order_id;

SELECT o.customer_id, o.order_id, o.order_date, o.total
FROM orders AS o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders AS o2
  WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id, o.order_id;

Time: O(n²) rows read
Space: O(1)
Why: The subquery depends on the outer row, so it runs once for each of the n orders and reads the whole table each time.

Where it breaks: speed and ties. At 20,000 orders our test run took 23 seconds. And customer 2 comes back twice, because orders 109 and 110 are both on the latest date.

The idea: Find each customer's latest date once with GROUP BY, then join orders to it to get the whole row.

The waste in Okay: it works out customer 1’s latest date three times, once for each of their orders. Work it out once per customer instead.

On the rows above: the subquery m has 3 rows, (1, 2026-10-11), (2, 2026-10-14) and (3, 2026-09-21). Then each order is matched against m. Orders 108, 109, 110 and 104 match.

Latest date per customer, then join back

FROM orders
GROUP BY
MAX(order_date)
JOIN
ORDER BY

orders
customer_id	order_id	order_date
1	101	2026-09-02
2	102	2026-09-05
1	103	2026-09-20
3	104	2026-09-21
2	106	2026-10-03
1	108	2026-10-11
2	109	2026-10-14
2	110	2026-10-14

Step 1 of 12. The subquery m runs first. FROM orders reads every row: 8 orders.

Speed Ask me first Show as text

1SELECT o.customer_id, o.order_id, o.order_date, o.total
2FROM orders AS o
3JOIN (
4  SELECT customer_id, MAX(order_date) AS latest
5  FROM orders
6  GROUP BY customer_id
7) AS m
8  ON m.customer_id = o.customer_id
9 AND m.latest = o.order_date
10ORDER BY o.customer_id, o.order_id;

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id, o.order_date, o.total
FROM orders AS o
JOIN (
  SELECT customer_id, MAX(order_date) AS latest
  FROM orders
  GROUP BY customer_id
) AS m
  ON m.customer_id = o.customer_id
 AND m.latest = o.order_date
ORDER BY o.customer_id, o.order_id;

Time: O(n), two passeswas O(n²)
Space: O(c) for c customers
Why: One pass groups the orders by customer; a second pass joins every order to its customer's latest date. Postgres does both with hash tables.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date, o.total
FROM orders AS o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders AS o2
  WHERE o2.customer_id = o.customer_id
)
JOIN (
  SELECT customer_id, MAX(order_date) AS latest
  FROM orders
  GROUP BY customer_id
) AS m
  ON m.customer_id = o.customer_id
 AND m.latest = o.order_date
ORDER BY o.customer_id, o.order_id;

Where it breaks: ties still come back twice: the join keeps every order on the latest date. It also reads orders twice, and it can’t be bent into “the latest 2 orders”.

The idea: ROW_NUMBER numbers each customer's orders newest first, with order_id to break ties; keep row number 1.

The waste in Better: two passes, and a second step to fix ties. ROW_NUMBER gives every row of a group its own number, so exactly one row per customer is number 1.

On the rows above: customer 1’s orders are numbered 108 → 1, 103 → 2, 101 → 3. Customer 2’s start again at 1: 110 → 1, 109 → 2 (same date, lower id), 106 → 3, 102 → 4. Customer 3 has only 104 → 1. Keeping rn = 1 gives 108, 110 and 104.

Highlighted lines are new in this rung.

SELECT customer_id, order_id, order_date, total
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders AS o
) AS ranked
WHERE rn = 1
ORDER BY customer_id;

Time: O(n log n); O(n) with the index belowwas O(n), two passes
Space: O(n) for the sort
Why: Postgres sorts the rows by customer and date, then numbers them in one pass. With the index, rows come out already sorted, so there's no sort at all.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date, o.total
FROM orders AS o
JOIN (
  SELECT customer_id, MAX(order_date) AS latest
  FROM orders
  GROUP BY customer_id
) AS m
  ON m.customer_id = o.customer_id
 AND m.latest = o.order_date
ORDER BY o.customer_id, o.order_id;
SELECT customer_id, order_id, order_date, total
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders AS o
) AS ranked
WHERE rn = 1
ORDER BY customer_id;

When Best isn’t best: in Postgres, DISTINCT ON says the same thing in fewer lines (see below). And when the question wants every tied row, ROW_NUMBER is wrong on purpose: use RANK instead (practice problem 2).

Make it fast with an index

Best’s only real cost is the sort. An index that stores each customer’s orders newest first removes it: Postgres reads the rows already in order and numbers them as they come.

-- Rows for one customer sit together, newest first, so the window
-- reads them in order instead of sorting the whole table.
CREATE INDEX orders_customer_latest
  ON orders (customer_id, order_date DESC, order_id DESC);

In our test run on PGlite (Postgres in the browser) with 20,000 orders and 2,000 customers:

Rung	No index	With the index
Okay	23.3 s	67 ms
Better	41 ms	41 ms
Best	19 ms	15 ms

Your numbers will differ. The gaps won’t. The index fixes Okay’s speed, but not its ties.

The Postgres shortcut: DISTINCT ON

DISTINCT ON (customer_id) keeps the first row of each customer, in the order ORDER BY gives. Same result as Best, in Postgres only. Its ORDER BY must start with the DISTINCT ON columns.

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check adds a few rows first, and then they’re removed again. Your query is saved in this browser for next time.

Try the Okay version first: it returns customer 2 twice. Then fix it.

Each customer's latest order

Ctrl + Enter runs

The tables

customers (customer_id, name, email, city, signed_up_on)
orders (order_id, customer_id, order_date, status, total)

Loads about 5 MB the first time. Best on Wi-Fi.

Not run yet: The store's data
○Not run yet: Asha (customer 1) orders again on 1 November
What this check changes
```
INSERT INTO orders VALUES (115, 1, '2026-11-01', 'placed', 9.99);
```
○Not run yet: Dana (customer 4) places a first order
What this check changes
```
INSERT INTO orders VALUES (116, 4, '2026-10-25', 'placed', 45.00);
```
○Not run yet: Hugo (customer 8) orders again on the day of the latest order: keep the higher order_id
What this check changes
```
INSERT INTO orders VALUES (120, 8, '2026-10-20', 'placed', 9.99);
```

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Rung	Time	Space	Good	Not so good	Use it when
Okay	O(n²) rows read	O(1)	Reads like the question	Very slow without an index; ties	A one-off query on a small table
Better	O(n)	O(c)	Works in every SQL database, even old ones	Ties come back twice; can’t do top 2	No window functions, ties impossible
Best	O(n log n), O(n) indexed	O(n)	One row per group, any N, ties handled	Needs a subquery or CTE for the filter	Almost always

How much work is that? Each bar below is one rung at the same number of orders, counted in rows read, with no index on any of them. Pick a size, or run the race.

Latest order: rows read at n orders

OkayA subquery for every row · O(n²)≈ 10,000,000,000 rows read
BetterLatest date, then join back · O(n)≈ 200,000 rows read
BestROW_NUMBER: one read, then a sort · O(n log n)≈ 100,000 rows read

At n = 100,000, Okay does about 100,000 times more work than Best.

Every bar does the same number of steps per second, so the race shows which rung finishes first. Turn on Log scale to compare the short bars.

Common mistakes and edge cases

You can’t filter on rn in the same query. WHERE rn = 1 next to the window function fails with column "rn" does not exist, because WHERE runs before window functions. Wrap the query in a subquery or a CTE, as Best does.
ROW_NUMBER, RANK or DENSE_RANK? ROW_NUMBER always gives 1, 2, 3, so exactly one row is first. RANK gives tied rows the same number (1, 1, 3), so it keeps every tied row. DENSE_RANK does the same without gaps (1, 1, 2).
NULLs sort first with DESC in Postgres. If the date column allowed NULL, a missing date would win. Write ORDER BY order_date DESC NULLS LAST. Here order_date is NOT NULL.
Customers with no orders don’t appear. If support wants them too, LEFT JOIN customers to the result, and their order columns will be NULL.
DISTINCT ON is Postgres only. MySQL 8, SQL Server and SQLite all have ROW_NUMBER, so Best works almost everywhere.

Say it in the interview

Questions to ask first

If a customer has two orders at the same latest time, do you want both, or one? Which one?
Should customers with no orders appear?
Which database? (DISTINCT ON is Postgres only.)
How big is the table, and is there an index on (customer_id, order_date)?

A 60-second walkthrough

“This is top-N per group. A subquery per row works, but it’s O(n²) without an index, and ties come back twice. I’d number each customer’s orders with ROW_NUMBER, partitioned by customer and ordered by date descending. order_id breaks ties, so exactly one row gets 1. Window functions run after WHERE, so I filter rn = 1 in an outer query. The cost is one sort; an index on customer_id, order_date descending, order_id descending removes it. In Postgres, DISTINCT ON is a shorter way to write the same thing.”

Likely follow-ups

The latest 3 orders? Change rn = 1 to rn <= 3.
Keep every order tied for latest? Use RANK() instead of ROW_NUMBER().
Few customers, millions of orders each? For each customer, a LATERAL subquery with ORDER BY ... LIMIT 1 can jump straight to the newest row through the index.
Each customer’s latest order in this table, plus their name? Join customers to the result, or join first and partition the same way.

Red flags interviewers notice

No tie-breaker, so the answer can change between runs.
WHERE rn = 1 in the same query as the window function.
GROUP BY customer_id with order_id in the SELECT list, which Postgres rejects.
Saying the correlated subquery “runs once” when it runs for every row.

Practice

Four related problems, from easy to hard. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. Each customer’s two latest orders (easy)

Return each customer’s two newest orders (or one, if they only have one): customer_id, order_id, order_date. Break ties on the date with the higher order_id.

Hint 1 of 2

Start from the lesson's Best query. Which one condition decides how many rows each customer keeps?

Hint 2 of 2

rn = 1 keeps one row per customer. What keeps two?

Each customer's two latest orders

Ctrl + Enter runs

The tables

orders (order_id, customer_id, order_date, status, total)

Loads about 5 MB the first time. Best on Wi-Fi.

Not run yet: The store's data

Hugo (customer 8) orders twice more on the day of the latest order

What this check changes

INSERT INTO orders VALUES (120, 8, '2026-10-20', 'placed', 9.99), (121, 8, '2026-10-20', 'placed', 5.00);

A big store: 20,000 orders (your query must finish in 5 seconds)

What this check changes

-- 2,000 more customers with 10 orders each: 20,000 orders, some on the same day, some cancelled.
INSERT INTO customers
  SELECT g, 'Customer ' || g, 'c' || g || '@example.com', 'Pune', date '2026-01-01'
  FROM generate_series(1001, 3000) AS g;
INSERT INTO orders
  SELECT 10000 + g, 1001 + g % 2000, date '2026-01-01' + (g * 37) % 300,
         CASE WHEN g % 7 = 0 THEN 'cancelled' ELSE 'delivered' END, 10 + g % 90
  FROM generate_series(1, 20000) AS g;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

The idea: Keep an order if fewer than 2 of the customer's orders are newer.

It handles ties, because it compares (order_date, order_id) pairs. But it reads the table once per row.

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
WHERE (
  SELECT COUNT(*)
  FROM orders AS newer
  WHERE newer.customer_id = o.customer_id
    AND (newer.order_date, newer.order_id) > (o.order_date, o.order_id)
) < 2
ORDER BY o.customer_id, o.order_date DESC, o.order_id DESC;

Time: O(n²) rows read
Space: O(1)
Why: A subquery per row counts the newer orders.

The idea: LEFT JOIN each order to the customer's newer orders, then keep groups with fewer than 2 matches.

Fine when each customer has a few orders. One customer with 10,000 orders makes 50 million pairs.

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
LEFT JOIN orders AS newer
  ON newer.customer_id = o.customer_id
 AND (newer.order_date, newer.order_id) > (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
HAVING COUNT(newer.order_id) < 2
ORDER BY o.customer_id, o.order_date DESC, o.order_id DESC;

Time: O(n × k), k = orders per customerwas O(n²)
Space: O(n × k)
Why: Each order joins only to its own customer's orders, but a customer with many orders makes many pairs.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
WHERE (
  SELECT COUNT(*)
  FROM orders AS newer
  WHERE newer.customer_id = o.customer_id
    AND (newer.order_date, newer.order_id) > (o.order_date, o.order_id)
) < 2
LEFT JOIN orders AS newer
  ON newer.customer_id = o.customer_id
 AND (newer.order_date, newer.order_id) > (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
HAVING COUNT(newer.order_id) < 2
ORDER BY o.customer_id, o.order_date DESC, o.order_id DESC;

The idea: The lesson's Best query with rn <= 2.

Changing N is changing one number. That’s why window functions are the standard answer for top-N per group.

Highlighted lines are new in this rung.

SELECT customer_id, order_id, order_date
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders AS o
) AS ranked
WHERE rn <= 2
ORDER BY customer_id, order_date DESC, order_id DESC;

Time: O(n log n); O(n) with the indexwas O(n × k)
Space: O(n)
Why: One sort and one numbering pass, whatever N is.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
LEFT JOIN orders AS newer
  ON newer.customer_id = o.customer_id
 AND (newer.order_date, newer.order_id) > (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
HAVING COUNT(newer.order_id) < 2
ORDER BY o.customer_id, o.order_date DESC, o.order_id DESC;
SELECT customer_id, order_id, order_date
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders AS o
) AS ranked
WHERE rn <= 2
ORDER BY customer_id, order_date DESC, order_id DESC;

2. The most expensive product in each category, ties included (medium)

Return the most expensive product of each category: category, name, price. If two products share the top price, return both.

Hint 1 of 3

Will ROW_NUMBER keep both of two tied products?

Hint 2 of 3

Which window function gives tied rows the same number?

Hint 3 of 3

RANK() gives 1, 1, 3 for a tie at the top. Keep the rows ranked 1.

The most expensive product in each category

Ctrl + Enter runs

The tables

products (product_id, name, category, price)

Loads about 5 MB the first time. Best on Wi-Fi.

Not run yet: The store's data
○Not run yet: A speaker costs the same as the headphones
What this check changes
```
INSERT INTO products VALUES (9, 'Speaker', 'electronics', 59.00);
```

A big store: 20,000 products (your query must finish in 5 seconds)

What this check changes

-- 20,000 more products in 2,000 categories, 10 each, at different prices.
INSERT INTO products
  SELECT 100 + g, 'Product ' || g, 'category ' || g % 2000, 5 + (g * 31) % 97
  FROM generate_series(1, 20000) AS g;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

The idea: Keep a product if its price equals its category's top price.

Here the tie is wanted, so matching on the price is right.

SELECT p.category, p.name, p.price
FROM products AS p
WHERE p.price = (
  SELECT MAX(p2.price)
  FROM products AS p2
  WHERE p2.category = p.category
)
ORDER BY p.category, p.name;

Time: O(n²) rows read
Space: O(1)
Why: The subquery runs once per product.

The idea: GROUP BY category for the top price, then join products to it.

The join keeps every product at the top price, which is exactly what this question asks.

Highlighted lines are new in this rung.

SELECT p.category, p.name, p.price
FROM products AS p
JOIN (
  SELECT category, MAX(price) AS top_price
  FROM products
  GROUP BY category
) AS m
  ON m.category = p.category
 AND m.top_price = p.price
ORDER BY p.category, p.name;

Time: O(n), two passeswas O(n²)
Space: O(categories)
Why: One pass to group, one to join.

What changed from the previous rung

SELECT p.category, p.name, p.price
FROM products AS p
WHERE p.price = (
  SELECT MAX(p2.price)
  FROM products AS p2
  WHERE p2.category = p.category
)
JOIN (
  SELECT category, MAX(price) AS top_price
  FROM products
  GROUP BY category
) AS m
  ON m.category = p.category
 AND m.top_price = p.price
ORDER BY p.category, p.name;

The idea: RANK() over each category by price, highest first; keep rank 1.

RANK gives tied rows the same rank, so both products are rank 1. ROW_NUMBER would quietly drop one of them.

Highlighted lines are new in this rung.

SELECT category, name, price
FROM (
  SELECT p.*,
         RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
  FROM products AS p
) AS ranked
WHERE rnk = 1
ORDER BY category, name;

Time: O(n log n)was O(n), two passes
Space: O(n)
Why: One sort and one ranking pass.

What changed from the previous rung

SELECT p.category, p.name, p.price
FROM products AS p
JOIN (
  SELECT category, MAX(price) AS top_price
  FROM products
  GROUP BY category
) AS m
  ON m.category = p.category
 AND m.top_price = p.price
ORDER BY p.category, p.name;
SELECT category, name, price
FROM (
  SELECT p.*,
         RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
  FROM products AS p
) AS ranked
WHERE rnk = 1
ORDER BY category, name;

3. Days since each customer’s previous order (medium)

For every order, return customer_id, order_id, order_date and days_since_previous: the days since the same customer’s previous order (by date, then order_id). A customer’s first order gets NULL.

Hint 1 of 3

You need a value from the row before, inside the same customer. Which window function reads the previous row?

Hint 2 of 3

LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id).

Hint 3 of 3

In Postgres, date minus date gives a whole number of days.

Days since the previous order

Ctrl + Enter runs

The tables

orders (order_id, customer_id, order_date, status, total)

Loads about 5 MB the first time. Best on Wi-Fi.

Not run yet: The store's data
○Not run yet: Asha (customer 1) orders again on 1 November
What this check changes
```
INSERT INTO orders VALUES (115, 1, '2026-11-01', 'placed', 9.99);
```

A big store: 20,000 orders (your query must finish in 5 seconds)

What this check changes

-- 2,000 more customers with 10 orders each: 20,000 orders, some on the same day, some cancelled.
INSERT INTO customers
  SELECT g, 'Customer ' || g, 'c' || g || '@example.com', 'Pune', date '2026-01-01'
  FROM generate_series(1001, 3000) AS g;
INSERT INTO orders
  SELECT 10000 + g, 1001 + g % 2000, date '2026-01-01' + (g * 37) % 300,
         CASE WHEN g % 7 = 0 THEN 'cancelled' ELSE 'delivered' END, 10 + g % 90
  FROM generate_series(1, 20000) AS g;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

The idea: For each order, find the customer's latest earlier date with a subquery.

Comparing (order_date, order_id) pairs makes the same-day orders work: the second one’s previous order is the first.

SELECT o.customer_id, o.order_id, o.order_date,
       o.order_date - (
         SELECT MAX(prev.order_date)
         FROM orders AS prev
         WHERE prev.customer_id = o.customer_id
           AND (prev.order_date, prev.order_id) < (o.order_date, o.order_id)
       ) AS days_since_previous
FROM orders AS o
ORDER BY o.customer_id, o.order_date, o.order_id;

Time: O(n²) rows read
Space: O(1)
Why: The subquery runs for every order.

The idea: LEFT JOIN each order to the customer's earlier orders and keep the latest date.

A LEFT JOIN keeps first orders, with NULL for the previous date.

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id, o.order_date,
       o.order_date - MAX(prev.order_date) AS days_since_previous
FROM orders AS o
LEFT JOIN orders AS prev
  ON prev.customer_id = o.customer_id
 AND (prev.order_date, prev.order_id) < (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
ORDER BY o.customer_id, o.order_date, o.order_id;

Time: O(n × k)was O(n²)
Space: O(n × k)
Why: Each order pairs with all its customer's earlier orders before MAX folds them.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date,
       o.order_date - (
         SELECT MAX(prev.order_date)
         FROM orders AS prev
         WHERE prev.customer_id = o.customer_id
           AND (prev.order_date, prev.order_id) < (o.order_date, o.order_id)
       ) AS days_since_previous
       o.order_date - MAX(prev.order_date) AS days_since_previous
FROM orders AS o
LEFT JOIN orders AS prev
  ON prev.customer_id = o.customer_id
 AND (prev.order_date, prev.order_id) < (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
ORDER BY o.customer_id, o.order_date, o.order_id;

The idea: LAG(order_date) reads the previous row in each customer's window.

LAG gives NULL when there is no previous row, so first orders need no special case.

Highlighted lines are new in this rung.

SELECT customer_id, order_id, order_date,
       order_date - LAG(order_date) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
       ) AS days_since_previous
FROM orders
ORDER BY customer_id, order_date, order_id;

Time: O(n log n)was O(n × k)
Space: O(n)
Why: One sort, then each row looks one row back.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date,
       o.order_date - MAX(prev.order_date) AS days_since_previous
FROM orders AS o
LEFT JOIN orders AS prev
  ON prev.customer_id = o.customer_id
 AND (prev.order_date, prev.order_id) < (o.order_date, o.order_id)
GROUP BY o.customer_id, o.order_id, o.order_date
ORDER BY o.customer_id, o.order_date, o.order_id;
SELECT customer_id, order_id, order_date,
       order_date - LAG(order_date) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
       ) AS days_since_previous
FROM orders
ORDER BY customer_id, order_date, order_id;

4. Running total per customer (hard)

For each order that isn’t cancelled, return customer_id, order_id, order_date, total and running_total: the sum of that customer’s totals so far, in order of date, then order_id. Cancelled orders don’t count.

Hint 1 of 3

SUM can be a window function too: SUM(total) OVER (...).

Hint 2 of 3

Where do cancelled orders get removed: before the window runs, or after?

Hint 3 of 3

Customer 2 has two orders on one day. With ORDER BY order_date alone, both get the same running total. Why? Add order_id, or say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Running total per customer

Ctrl + Enter runs

The tables

orders (order_id, customer_id, order_date, status, total)

Loads about 5 MB the first time. Best on Wi-Fi.

Not run yet: The store's data
○Not run yet: Ben's order 106 is cancelled
What this check changes
```
UPDATE orders SET status = 'cancelled' WHERE order_id = 106;
```

A big store: 20,000 orders (your query must finish in 5 seconds)

What this check changes

-- 2,000 more customers with 10 orders each: 20,000 orders, some on the same day, some cancelled.
INSERT INTO customers
  SELECT g, 'Customer ' || g, 'c' || g || '@example.com', 'Pune', date '2026-01-01'
  FROM generate_series(1001, 3000) AS g;
INSERT INTO orders
  SELECT 10000 + g, 1001 + g % 2000, date '2026-01-01' + (g * 37) % 300,
         CASE WHEN g % 7 = 0 THEN 'cancelled' ELSE 'delivered' END, 10 + g % 90
  FROM generate_series(1, 20000) AS g;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

The idea: For each order, sum the customer's orders up to and including it.

The subquery needs its own status <> 'cancelled' filter, or cancelled orders get into the sums.

SELECT o.customer_id, o.order_id, o.order_date, o.total,
       (
         SELECT SUM(earlier.total)
         FROM orders AS earlier
         WHERE earlier.customer_id = o.customer_id
           AND earlier.status <> 'cancelled'
           AND (earlier.order_date, earlier.order_id) <= (o.order_date, o.order_id)
       ) AS running_total
FROM orders AS o
WHERE o.status <> 'cancelled'
ORDER BY o.customer_id, o.order_date, o.order_id;

Time: O(n²) rows read
Space: O(1)
Why: The subquery runs for every order and reads the table.

The idea: Join each order to the customer's earlier orders and add them up with GROUP BY.

Correct, but the pairs grow fast for customers with many orders.

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id, o.order_date, o.total,
       SUM(earlier.total) AS running_total
FROM orders AS o
JOIN orders AS earlier
  ON earlier.customer_id = o.customer_id
 AND earlier.status <> 'cancelled'
 AND (earlier.order_date, earlier.order_id) <= (o.order_date, o.order_id)
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id, o.order_id, o.order_date, o.total
ORDER BY o.customer_id, o.order_date, o.order_id;

Time: O(n × k)was O(n²)
Space: O(n × k)
Why: Each order pairs with every earlier order of its customer.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date, o.total,
       (
         SELECT SUM(earlier.total)
         FROM orders AS earlier
         WHERE earlier.customer_id = o.customer_id
           AND earlier.status <> 'cancelled'
           AND (earlier.order_date, earlier.order_id) <= (o.order_date, o.order_id)
       ) AS running_total
       SUM(earlier.total) AS running_total
FROM orders AS o
JOIN orders AS earlier
  ON earlier.customer_id = o.customer_id
 AND earlier.status <> 'cancelled'
 AND (earlier.order_date, earlier.order_id) <= (o.order_date, o.order_id)
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id, o.order_id, o.order_date, o.total
ORDER BY o.customer_id, o.order_date, o.order_id;

The idea: SUM(total) OVER each customer's orders, in order, from the first row to this one.

WHERE runs before the window, so cancelled orders are gone before anything is summed. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW adds up row by row. The default frame (RANGE) would treat two orders with the same sort key as one step.

Highlighted lines are new in this rung.

SELECT customer_id, order_id, order_date, total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status <> 'cancelled'
ORDER BY customer_id, order_date, order_id;

Time: O(n log n)was O(n × k)
Space: O(n)
Why: One sort, then a running sum as the rows go by.

What changed from the previous rung

SELECT o.customer_id, o.order_id, o.order_date, o.total,
       SUM(earlier.total) AS running_total
FROM orders AS o
JOIN orders AS earlier
  ON earlier.customer_id = o.customer_id
 AND earlier.status <> 'cancelled'
 AND (earlier.order_date, earlier.order_id) <= (o.order_date, o.order_id)
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id, o.order_id, o.order_date, o.total
ORDER BY o.customer_id, o.order_date, o.order_id;
SELECT customer_id, order_id, order_date, total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status <> 'cancelled'
ORDER BY customer_id, order_date, order_id;

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

Predict the outputCustomer 2 has these orders. Which order gets rn = 2?

-- order_id | order_date
--   102    | 2026-09-05
--   106    | 2026-10-03
--   109    | 2026-10-14
--   110    | 2026-10-14
ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC, order_id DESC
) AS rn

106109110102

Spot the bugThis should return each customer's latest order. What goes wrong first?
```
SELECT customer_id, order_id,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY order_date DESC
       ) AS rn
FROM orders
WHERE rn = 1;
```
It fails: WHERE runs before window functions, so rn doesn't exist thereIt fails: PARTITION BY also needs a GROUP BYIt works, and returns one row per customerROW_NUMBER needs a column inside its brackets
Multiple choiceTwo products share the top price in their category, and the question wants both. Which function do you number with?
ROW_NUMBER()RANK()LAG()COUNT(*)
Pick the patternYou need each user's 3 most recent logins. Which fits?
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_at DESC), then rn <= 3ORDER BY login_at DESC LIMIT 3GROUP BY user_id with MAX(login_at)SELECT DISTINCT user_id, login_at
Fill in the answerFill in the blank: ROW_NUMBER() OVER (____ BY customer_id ORDER BY order_date DESC) numbers each customer's orders on their own.Your answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

Cheat sheet

Top N per group: ROW_NUMBER() OVER (PARTITION BY group ORDER BY ...), then WHERE rn <= N in an outer query.
Always end the window’s ORDER BY with a unique column, so ties are decided.
Want every tied row? RANK(). Exactly one? ROW_NUMBER().
Window functions run after WHERE: filter on them one level up.
Postgres shortcut for N = 1: DISTINCT ON (group) ... ORDER BY group, ....
Index (group, sort columns) in the window’s order to skip the sort.

Go deeper

Window functions tutorial, from the PostgreSQL docs.
Window function list: ROW_NUMBER, RANK, LAG and the rest.
DISTINCT ON, in the SELECT reference.
Next on the SQL roadmap: Indexes and Reading EXPLAIN plans.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

Return the latest row per group three ways, and give the cost of each
Number rows inside each group with ROW_NUMBER and PARTITION BY
Break ties on purpose, so each customer gets exactly one row
Choose ROW_NUMBER, RANK or DISTINCT ON for the question asked

Status: Not started