Bigger than the order before, or just typed in late?

See the problem

Each week, the shop thanks customers whose new order is bigger than their last one. The list came from this query:

Orders bigger than the one before

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND p.order_id < o.order_id
    ORDER BY p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

On the store’s data, it lists orders 103, 106, 110 and 111, and the list was right.

Then Asha phoned in an order on 1 October, for 50.00, and it was typed in late as order 115. Her order before that, by date, was 103, at 104.00. So 115 isn’t bigger. But the next list thanked her for it.

Define the task

List the orders that are bigger than the same customer’s order just before them: order_id, customer_id and total, by order_id.

• “Before” means by order_date. Two orders on the same day go by order_id.
• Cancelled orders aren’t listed, and don’t count as the order before.
• A customer’s first order has nothing before it, so it isn’t listed.

Pause and think

⏸ Pause and think

The subquery finds the order before by the highest order_id below this one. Order 115 was typed in late. Which of Asha’s orders has the highest order_id below 115?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

Order 108, from 11 October, has the highest order_id below 115. But 115 is dated 1 October, so 108 came after it.

Hint 2 of 3

Compare by date, then by id for a tie: (p.order_date, p.order_id) < (o.order_date, o.order_id). ORDER BY the same two, newest first.

Hint 3 of 3

The subquery has its own WHERE. The outer status rule doesn’t reach into it, so add p.status <> 'cancelled' there too.

Understand the idea

A correlated subquery reads the outer row. In WHERE p.customer_id = o.customer_id, the o comes from the outer query. So the subquery’s answer is different for each order. Postgres runs it again for each row that reaches it. It’s like asking “what did this customer order last time?” once for every order on the list.

★Order by what you mean, not by what looks the same

An id often grows with the date, so ordering by id seems to work. But a late entry, or an imported row, breaks that. When the task says “before”, order by the date, and add the id only to break ties.

No match gives NULL. A subquery used as a value gives NULL when it finds no row. Then o.total > NULL isn’t true, so the row goes. Here that’s what we want: a first order has nothing to beat.

It must give one value. A subquery used as a value must give at most one row. With two, Postgres stops: “more than one row returned by a subquery used as an expression”. ORDER BY … LIMIT 1 or an aggregate like max() makes sure of it.

Name your tables. Inside the subquery, a bare customer_id means the subquery’s own table. WHERE customer_id = customer_id compares a row with itself, so it’s always true. Give both tables a short name, like o and p, and use it on every column.

Watch the subquery run once for each order. Then type your own, like a cancelled order just before a new one:

The growing orders, step by step

⤢ Full view

1. FROM orders o
2. WHERE o.status <> 'cancelled'
3. For each row: o.total > (the subquery)
4. ORDER BY o.order_id

orders o

order_id	customer_id	order_date	total	status
101	1	2026-09-02	29.89	delivered
103	1	2026-09-20	104.00	delivered
108	1	2026-10-11	36.99	shipped
115	1	2026-10-01	50.00	delivered
112	7	2026-10-16	9.99	delivered
116	7	2026-10-17	5.00	cancelled
117	7	2026-10-18	9.00	placed

Step 1 of 9. FROM orders o reads all 7 rows. The subquery will read orders too, under the name p.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT o.order_id, o.customer_id, o.total
2FROM orders o
3WHERE o.status <> 'cancelled'
4  AND o.total > (
5    SELECT p.total
6    FROM orders p
7    WHERE p.customer_id = o.customer_id
8      AND p.status <> 'cancelled'
9      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
10    ORDER BY p.order_date DESC, p.order_id DESC
11    LIMIT 1
12  )
13ORDER BY o.order_id;

Try your own input

Orders: order_id, customer_id, order_date, total, status101, 1, 2026-09-02, 29.89, delivered 103, 1, 2026-09-20, 104.00, delivered 108, 1, 2026-10-11, 36.99, shipped 115, 1, 2026-10-01, 50.00, delivered 112, 7, 2026-10-16, 9.99, delivered 116, 7, 2026-10-17, 5.00, cancelled 117, 7, 2026-10-18, 9.00, placedShow it

Climb the ladder

All three give 103, 106, 110 and 111 on the store’s data. What changes is which order counts as “the one before”.

Okay

The order before, by order_id

The idea: For each order, pick the same customer’s order with the highest order_id below it.

It looks right because, in the store, every order got its id on the day it was placed.

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND p.order_id < o.order_id
    ORDER BY p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

Gets right

Ids in date order, as in the store

Gets wrong

An order typed in late

Why

It trusts the id to follow the date. A late entry gets a high id but an early date.

Where it breaks: Asha’s order 115, dated 1 October, is typed in late. Okay compares it with 108, at 36.99, and lists it. Okay passes 1 of the 3 checks below.

Better

The order before, by date

The idea: Pick the order just before by (order_date, order_id), newest first.

The gap in Okay: it ordered by id, but the task is about time.

Highlighted lines are new in this rung.

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
    ORDER BY p.order_date DESC, p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

Gets right

An order typed in latewas Ids in date order, as in the store

Gets wrong

A cancelled order just before

Why

The outer WHERE drops cancelled orders from the list, but the subquery still picks one as the order before.

What changed from the previous rung

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND p.order_id < o.order_id
    ORDER BY p.order_id DESC
      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
    ORDER BY p.order_date DESC, p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

Where it breaks: Grace cancels a 5.00 order, then places one for 9.00. Better compares 9.00 with the cancelled 5.00 and lists it. Her real last order was 9.99. Better passes 2 of 3.

Best

By date, and skip cancelled orders

The idea: Add p.status <> 'cancelled' inside the subquery, too.

The gap in Better: the status rule was in the outer query only.

Highlighted lines are new in this rung.

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND p.status <> 'cancelled'
      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
    ORDER BY p.order_date DESC, p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

Gets right

Every checkwas An order typed in late

Gets wrong

Nothing the checks try

Why

The subquery orders by date with an id tiebreak, and has its own status rule.

What changed from the previous rung

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total > (
    SELECT p.total
    FROM orders p
    WHERE p.customer_id = o.customer_id
      AND p.status <> 'cancelled'
      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
    ORDER BY p.order_date DESC, p.order_id DESC
    LIMIT 1
  )
ORDER BY o.order_id;

When Best isn’t best: the subquery runs once for each order. With many orders, it needs an index on (customer_id, order_date). lag(total) OVER (PARTITION BY customer_id ORDER BY order_date, order_id), a window function from a later lesson, reads the table once.

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 changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

Orders bigger than the one before

Ctrl + Enter runs

The tables

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

-- Orders bigger than the same customer's order just before them, by date. Cancelled orders don't count. SELECT o.order_id, o.customer_id, o.order_date, o.total, o.status FROM orders o ORDER BY o.customer_id, o.order_date, o.order_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Asha’s order 115, dated 1 October, is typed in late (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-01', 'delivered', 50.00);

3. ○Not run yet: The late order, and Grace cancels a 5.00 order, then places one for 9.00 (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-01', 'delivered', 50.00);
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (116, 7, '2026-10-17', 'cancelled', 5.00), (117, 7, '2026-10-18', 'placed', 9.00);

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

Compare

Way	Gets right	Gets wrong	Use it when
Okay: before by order_id	Ids in date order	An order typed in late	Ids are given in time order, and always will be
Better: before by date	An order typed in late	A cancelled order just before	No row needs to be skipped
Best: by date, skip cancelled	Every check	Nothing the checks try	The usual answer, with an index on (customer_id, order_date)

Common mistakes and edge cases

!The outer WHERE doesn’t reach into the subquery

The subquery reads the whole table again, with only its own WHERE. A rule like “cancelled orders don’t count” must be written inside it too.

• A bare column inside the subquery means the inner table. WHERE customer_id = customer_id is always true. Name both tables and use the names.
• Two rows stop the query. A subquery used as a value must give one row at most. Use LIMIT 1 or an aggregate.
• No row gives NULL. total > NULL isn’t true, so the row goes. If you want to keep it, use coalesce(…, 0) or a LEFT JOIN.
• Same-day ties. Ben has two orders on 14 October. Ordering by date alone picks either one. Add order_id to break the tie.
• It runs once per row. For 1,000 orders, that’s 1,000 small lookups. An index on the columns the subquery filters on makes each one fast.

Say it in the interview

Questions to ask first

• What does “before” mean: by date, or by id? What about two orders on the same day?
• Do cancelled orders count as the order before?
• What about a customer’s first order?

A 60-second walkthrough

“For each order that isn’t cancelled, a correlated subquery finds the same customer’s order just before it. It filters on o.customer_id, skips cancelled orders, and keeps orders earlier by (order_date, order_id). ORDER BY the same two, descending, with LIMIT 1, gives the one just before. If there’s none, it’s NULL, and the comparison drops the row. An index on (customer_id, order_date) keeps each lookup fast.”

Likely follow-ups

• Can you do it without a subquery? Yes: lag(total) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) in a CTE that skips cancelled orders, then compare. It reads the table once.
• Does Postgres really run it once per row? The plan says so: a “SubPlan” runs for each outer row. Sometimes Postgres turns it into a join instead, for EXISTS and IN.
• Why not max(order_date)? Then you need a second lookup for that order’s total, and same-day ties give two rows.

Practice

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

1. When each product last sold (easy)

The stock team wants every product with the date it last sold, or NULL if it never has. Cancelled orders don’t count. List product_id, name and last_sold, by product_id.

Hint 1 of 3

A JOIN to order_items drops a product nobody has bought. Start from products, and keep every row.

Hint 2 of 3

Put the lookup in SELECT: (SELECT max(o.order_date) FROM order_items i JOIN orders o … WHERE i.product_id = p.product_id). It gives NULL when nothing matches.

Hint 3 of 3

The subquery needs its own o.status <> 'cancelled'.

When each product last sold

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)

SELECT p.product_id, p.name FROM products p ORDER BY p.product_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: every product has sold (row order matters)
2. ○Not run yet: A new kite that nobody has bought yet (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price) VALUES (9, 'Kite', 'toys', 15.00);

3. ○Not run yet: The kite, and Chen orders one but cancels (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price) VALUES (9, 'Kite', 'toys', 15.00);
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-10-21', 'cancelled', 15.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (115, 9, 1, 15.00);

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

Okay

JOIN, then max

The idea: Join products to their sales, and take the latest date per product.

Okay passes 1 of 3. When the shop adds a kite nobody has bought, it’s missing from the list.

SELECT p.product_id, p.name, max(o.order_date) AS last_sold
FROM products p
JOIN order_items i ON i.product_id = p.product_id
JOIN orders o ON o.order_id = i.order_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id;

Gets right

Every product sold, as in the store

Gets wrong

A product nobody has bought

Why

The JOIN keeps only products with a sale, so one with none has no row.

Better

A correlated subquery in SELECT

The idea: For each product, look up its latest sale date in a subquery.

The gap in Okay: the JOIN dropped products with no sale.

Highlighted lines are new in this rung.

SELECT p.product_id, p.name,
  (SELECT max(o.order_date)
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE i.product_id = p.product_id) AS last_sold
FROM products p
ORDER BY p.product_id;

Gets right

A product nobody has bought

Gets wrong

A sale that was cancelled

Why

The subquery counts every order, cancelled ones too.

What changed from the previous rung

SELECT p.product_id, p.name, max(o.order_date) AS last_sold
SELECT p.product_id, p.name,
  (SELECT max(o.order_date)
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE i.product_id = p.product_id) AS last_sold
FROM products p
JOIN order_items i ON i.product_id = p.product_id
JOIN orders o ON o.order_id = i.order_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id;

Where it breaks: Chen orders the kite on 21 October, then cancels. Better says the kite last sold that day. Better passes 2 of 3.

Best

Skip cancelled orders in the subquery

The idea: Add o.status <> 'cancelled' inside the subquery.

The gap in Better: a cancelled sale counted.

Highlighted lines are new in this rung.

SELECT p.product_id, p.name,
  (SELECT max(o.order_date)
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE i.product_id = p.product_id
     AND o.status <> 'cancelled') AS last_sold
FROM products p
ORDER BY p.product_id;

Gets right

Every checkwas A product nobody has bought

Gets wrong

Nothing the checks try

Why

Every product gets a row, and only real sales count.

What changed from the previous rung

SELECT p.product_id, p.name,
  (SELECT max(o.order_date)
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE i.product_id = p.product_id) AS last_sold
   WHERE i.product_id = p.product_id
     AND o.status <> 'cancelled') AS last_sold
FROM products p
ORDER BY p.product_id;

When Best isn’t best: for a few columns from the same sales, a LEFT JOIN to a grouped subquery reads the sales once, not once per product.

2. Whose latest order was cancelled? (medium)

The support team calls customers whose latest order was cancelled. “Latest” is by order_date, then order_id. List customer_id and order_id, by customer_id.

Hint 1 of 3

Each customer has their own latest order. One latest order for the whole shop isn’t enough.

Hint 2 of 3

A correlated subquery can find each customer’s latest order: WHERE l.customer_id = o.customer_id.

Hint 3 of 3

max(order_date) ties when a customer has two orders on one day. Pick the order_id with ORDER BY l.order_date DESC, l.order_id DESC LIMIT 1.

Whose latest order was cancelled?

Ctrl + Enter runs

The tables

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

SELECT customer_id, order_id, order_date, status FROM orders ORDER BY customer_id, order_date, order_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: no one’s latest order is cancelled (row order matters)
2. ○Not run yet: Grace cancels order 112, her only order (row order matters)
   What this check changes

   UPDATE orders SET status = 'cancelled' WHERE order_id = 112;

3. ○Not run yet: Grace cancels 112, and Ben cancels 109, one of his two orders that day (row order matters)
   What this check changes

   UPDATE orders SET status = 'cancelled' WHERE order_id IN (112, 109);

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

Okay

The shop’s latest order

The idea: Take the newest order in the shop, and list it if it was cancelled.

Okay passes 1 of 3. When Grace cancels order 112, her only order, Okay misses her: the shop’s latest order is Hugo’s.

SELECT customer_id, order_id
FROM (
  SELECT customer_id, order_id, status
  FROM orders
  ORDER BY order_date DESC, order_id DESC
  LIMIT 1
) AS latest
WHERE status = 'cancelled'
ORDER BY customer_id;

Gets right

No one to call, as in the store

Gets wrong

A customer whose last order isn’t the shop’s last

Why

It finds one latest order for the whole shop, not one per customer.

Better

Each customer’s latest date

The idea: Keep a cancelled order when its date is the customer’s latest date.

The gap in Okay: it didn’t look per customer.

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id
FROM orders o
WHERE o.status = 'cancelled'
  AND o.order_date = (
    SELECT max(l.order_date)
    FROM orders l
    WHERE l.customer_id = o.customer_id
  )
ORDER BY o.customer_id;

Gets right

A customer whose last order isn’t the shop’s last

Gets wrong

Two orders on the same day

Why

max(order_date) can match two orders. A cancelled one passes even when the other, later one didn’t cancel.

What changed from the previous rung

SELECT customer_id, order_id
FROM (
  SELECT customer_id, order_id, status
  FROM orders
  ORDER BY order_date DESC, order_id DESC
  LIMIT 1
) AS latest
WHERE status = 'cancelled'
ORDER BY customer_id;
SELECT o.customer_id, o.order_id
FROM orders o
WHERE o.status = 'cancelled'
  AND o.order_date = (
    SELECT max(l.order_date)
    FROM orders l
    WHERE l.customer_id = o.customer_id
  )
ORDER BY o.customer_id;

Where it breaks: Ben cancels 109, but 110, on the same day with a higher id, still stands. Better lists Ben. Better passes 2 of 3.

Best

Each customer’s latest order_id

The idea: Pick each customer’s latest order with ORDER BY date, then id, LIMIT 1, and compare ids.

The gap in Better: a date can tie, an id can’t.

Highlighted lines are new in this rung.

SELECT o.customer_id, o.order_id
FROM orders o
WHERE o.status = 'cancelled'
  AND o.order_id = (
    SELECT l.order_id
    FROM orders l
    WHERE l.customer_id = o.customer_id
    ORDER BY l.order_date DESC, l.order_id DESC
    LIMIT 1
  )
ORDER BY o.customer_id;

Gets right

Every checkwas A customer whose last order isn’t the shop’s last

Gets wrong

Nothing the checks try

Why

Comparing order ids picks exactly one latest order per customer, ties broken by id.

What changed from the previous rung

SELECT o.customer_id, o.order_id
FROM orders o
WHERE o.status = 'cancelled'
  AND o.order_date = (
    SELECT max(l.order_date)
  AND o.order_id = (
    SELECT l.order_id
    FROM orders l
    WHERE l.customer_id = o.customer_id
    ORDER BY l.order_date DESC, l.order_id DESC
    LIMIT 1
  )
ORDER BY o.customer_id;

When Best isn’t best: DISTINCT ON (customer_id) with the same ORDER BY, or row_number(), finds every customer’s latest order in one pass.

3. Each customer’s biggest order (medium)

The account team wants each customer’s biggest order, all of them if two tie. Cancelled orders don’t count. List order_id, customer_id and total, by customer_id, then order_id.

Hint 1 of 3

total IN (each customer’s max) mixes customers: a total equal to someone else’s max passes.

Hint 2 of 3

Compare with the same customer’s max: o.total = (SELECT max(b.total) FROM orders b WHERE b.customer_id = o.customer_id …).

Hint 3 of 3

LIMIT 1 keeps one order, so a tie loses one. Comparing with max keeps both.

Each customer’s biggest order

Ctrl + Enter runs

The tables

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

SELECT customer_id, max(total) AS biggest FROM orders WHERE status <> 'cancelled' GROUP BY customer_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Asha places a 69.00 order: the same as someone else’s biggest (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-21', 'placed', 69.00);

3. ○Not run yet: Asha’s 69.00 order, and Hugo places a second order equal to his biggest (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-21', 'placed', 69.00), (116, 8, '2026-10-22', 'placed', 142.99);

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

Okay

total IN (every customer’s max)

The idea: Keep an order whose total is some customer’s biggest.

Okay passes 1 of 3. When Asha places a 69.00 order, Ben’s biggest, it’s listed as one of hers.

SELECT order_id, customer_id, total
FROM orders
WHERE status <> 'cancelled'
  AND total IN (
    SELECT max(total)
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY customer_id
  )
ORDER BY customer_id, order_id;

Gets right

No total equal to another customer’s max, as in the store

Gets wrong

A total equal to another customer’s max

Why

The list holds only amounts. It forgets which customer each max came from.

Better

The customer’s top order, LIMIT 1

The idea: For each order, look up the same customer’s biggest order_id, and keep a match.

The gap in Okay: the list forgot the customer.

Highlighted lines are new in this rung.

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.order_id = (
  SELECT b.order_id
  FROM orders b
  WHERE b.customer_id = o.customer_id
    AND b.status <> 'cancelled'
  ORDER BY b.total DESC, b.order_id
  LIMIT 1
)
ORDER BY o.customer_id, o.order_id;

Gets right

A total equal to another customer’s max

Gets wrong

Two orders tied for biggest

Why

LIMIT 1 picks one order, so a tie loses the other.

What changed from the previous rung

SELECT order_id, customer_id, total
FROM orders
WHERE status <> 'cancelled'
  AND total IN (
    SELECT max(total)
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY customer_id
  )
ORDER BY customer_id, order_id;
SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.order_id = (
  SELECT b.order_id
  FROM orders b
  WHERE b.customer_id = o.customer_id
    AND b.status <> 'cancelled'
  ORDER BY b.total DESC, b.order_id
  LIMIT 1
)
ORDER BY o.customer_id, o.order_id;

Where it breaks: Hugo places a second 142.99 order. Both are his biggest, but Better lists one. Better passes 2 of 3.

Best

total = the customer’s max

The idea: Keep an order whose total equals the same customer’s max.

The gap in Better: LIMIT 1 cut a tie.

Highlighted lines are new in this rung.

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.total = (
    SELECT max(b.total)
    FROM orders b
    WHERE b.customer_id = o.customer_id
      AND b.status <> 'cancelled'
  )
ORDER BY o.customer_id, o.order_id;

Gets right

Every checkwas A total equal to another customer’s max

Gets wrong

Nothing the checks try

Why

The max is per customer, and every order equal to it passes.

What changed from the previous rung

SELECT o.order_id, o.customer_id, o.total
FROM orders o
WHERE o.order_id = (
  SELECT b.order_id
  FROM orders b
  WHERE b.customer_id = o.customer_id
    AND b.status <> 'cancelled'
  ORDER BY b.total DESC, b.order_id
  LIMIT 1
)
WHERE o.status <> 'cancelled'
  AND o.total = (
    SELECT max(b.total)
    FROM orders b
    WHERE b.customer_id = o.customer_id
      AND b.status <> 'cancelled'
  )
ORDER BY o.customer_id, o.order_id;

When Best isn’t best: rank() OVER (PARTITION BY customer_id ORDER BY total DESC) = 1 reads orders once. A later lesson covers window functions.

Check yourself

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

1. Predict the outputAsha has three orders. What does this give?

   SELECT c.name,
     (SELECT o.total FROM orders o WHERE o.customer_id = c.customer_id) AS total
   FROM customers c;

   One row per customer, with their first order’s totalIt stops with an error: more than one row returned by a subquery used as an expressionOne row per orderOne row per customer, with NULL for Asha

   Check answer
2. Spot the bugThis should list orders bigger than the same customer’s average. What does it really compare with?

   SELECT o.order_id
   FROM orders o
   WHERE o.total > (
     SELECT avg(total) FROM orders WHERE customer_id = customer_id
   );

   The same customer’s averageNothing: it stops with an errorThe average over every order in the shopNULL, so no row is listed

   Check answer
3. Multiple choiceA correlated subquery finds no row for a customer’s first order. What is o.total > (that subquery)?

   NULL, so WHERE drops the rowtrue, since any total beats nothingfalse, and the row is keptAn error: the subquery found no row

   Check answer
4. Multiple choiceIn the lesson’s query, how many times does the subquery run, as you read it?

   Once, before the outer queryOnce for each customerOnce for each row of ordersOnce for each row the outer WHERE keeps

   Check answer
5. Multiple choiceWhich index helps the lesson’s subquery most?

   An index on totalAn index on statusAn index on (customer_id, order_date)No index helps a subquery

   Check answer

Flashcards

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

• Card 1What makes a subquery correlated?Show answer
• Card 2What does a subquery used as a value give when no row matches?Show answer
• Card 3What if a subquery used as a value finds two rows?Show answer
• Card 4Does the outer WHERE apply inside a subquery?Show answer
• Card 5How do you pick “the row before” safely?Show answer

Add 5 cards to my review deck

Cheat sheet

• A correlated subquery reads a column of the outer row, like o.customer_id.
• It runs once for each row that reaches it. Index the columns it filters on.
• No row gives NULL. Two rows stop the query. Use LIMIT 1 or an aggregate.
• Name both tables, and use the names on every column.
• The outer WHERE doesn’t reach into the subquery. Repeat the rules it needs.
• “Before” means by date, with the id only as a tiebreak.

Go deeper

• In the PostgreSQL docs: scalar subqueries.
• Try your own orders: the growing orders on the SQL visualizer.
• Next on the SQL roadmap: the whole map, with EXISTS, IN and JOIN, three ways to ask “is there a match?”.

Can you do these now?

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

• Write a subquery that reads a column of the outer row
• Say how many times it runs, and what it gives when nothing matches
• Pick the row before by date, with a tiebreak, not by id
• Spot when a JOIN or a window function would do the same job

✓Mark as completeI already know this

Status: Not started