Elif bought two backpacks and got the bottle offer twice

See the problem

The shop sends an offer to customers who bought a backpack but never a water bottle: the backpack has a pocket for one. This query finds them:

The bottle offer’s query

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 8
  AND c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
  )
ORDER BY c.customer_id;

It found Elif, who bought a backpack in order 111. Then, on 21 October, Elif bought a second backpack, as a gift: order 115.

The next offer went out, and Elif got it twice.

Define the task

List each customer who has bought a backpack (product 8) but never a water bottle (product 4): customer_id and name, by customer_id.

• Each customer once, however many backpacks they bought.
• A cancelled order wasn’t bought, so it doesn’t count, for either item.

Pause and think

⏸ Pause and think

Elif has two backpack orders, and Okay’s JOIN gives one row for each order that matches. How many rows does Elif get? And how many would WHERE customer_id IN (…) give her, with 5 on the list twice?

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

Hints open one at a time:

Hint 1 of 3

A JOIN gives a row for each backpack order, so a customer with two comes back twice.

Hint 2 of 3

WHERE customer_id IN (SELECT …) asks one question for each customer: are they on the list? Each customer comes back once.

Hint 3 of 3

Add o.status <> 'cancelled' inside each subquery. The outer query reads customers, which have no status.

Understand the idea

A subquery is a query inside another, in brackets. It runs first, and the outer query uses its answer. What the answer is decides where it can go: one value, a list, or a whole table.

One value: compare with it. SELECT name, price FROM products WHERE price > (SELECT avg(price) FROM products) compares each price with the average, 22.085: the desk lamp, the headphones and the backpack are above it. A subquery used as one value must give one row. With none, it gives NULL, so nothing matches. With two or more, Postgres stops: “more than one row returned by a subquery used as an expression”.

A list: use it with IN. WHERE customer_id IN (SELECT o.customer_id FROM orders o JOIN order_items i …) asks one question for each customer: are they on the list? So each customer comes back once, however often they’re on it. A JOIN gives a row for each match instead: Elif, with two backpack orders, comes back twice. NOT IN keeps the customers who aren’t on the list. A NULL on the list makes it keep no one, as in the NULL lesson, but orders.customer_id can’t be NULL, so this list is safe.

Each query has its own WHERE. A filter in the outer query doesn’t reach into a subquery. In the bottle offer, the outer query reads customers, which have no status, so “not cancelled” has to go inside each subquery.

Watch the offer run on a few orders. Then type your own, like a customer with three backpacks, or a water bottle someone cancelled:

The bottle offer, step by step

⤢ Full view

1. First subquery: backpacks
2. Second subquery: water bottles
3. FROM customers
4. WHERE customer_id IN (…)
5. AND customer_id NOT IN (…)
6. SELECT customer_id, name
7. ORDER BY customer_id

orders o JOIN order_items i

Kept?	customer_id	product_id	status
✓kept	5	8	delivered
✓kept	5	8	placed
✓kept	7	8	placed
✗dropped	7	4	cancelled
✗dropped	3	8	cancelled
✓kept	1	8	delivered
✗dropped	1	4	shipped

Step 1 of 7. The first subquery runs first. Of the 7 orders, it keeps the backpacks (product 8) that weren’t cancelled. The backpack order from customer 3 is cancelled. Its list: (5, 5, 7, 1).

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, name
2FROM customers
3WHERE customer_id IN (
4    SELECT o.customer_id
5    FROM orders o
6    JOIN order_items i ON i.order_id = o.order_id
7    WHERE i.product_id = 8
8      AND o.status <> 'cancelled'
9  )
10  AND customer_id NOT IN (
11    SELECT o.customer_id
12    FROM orders o
13    JOIN order_items i ON i.order_id = o.order_id
14    WHERE i.product_id = 4
15      AND o.status <> 'cancelled'
16  )
17ORDER BY customer_id;

Try your own input

Orders: customer_id, item, status5, backpack, delivered 5, backpack, placed 7, backpack, placed 7, bottle, cancelled 3, backpack, cancelled 1, backpack, delivered 1, bottle, shippedShow it

A table: put it in FROM. A subquery in FROM is a table made just for this query. Name it, like AS a, to read its columns. Postgres 16 and later let you leave the name out, but many databases don’t. The classic “orders above the customer’s own average” works this way: average each customer’s orders in a subquery, join it back to orders, and compare.

Orders above the customer’s own average

SELECT o.customer_id, o.order_id, o.total, round(a.avg_total, 2) AS avg_total
FROM orders o
JOIN (
  SELECT customer_id, avg(total) AS avg_total
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
) AS a ON a.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
  AND o.total > a.avg_total
ORDER BY o.customer_id, o.order_id;

It gives Asha’s order 103, Ben’s 106 and 110, and Elif’s 111. A customer with only one order that wasn’t cancelled, like Chen, never shows up: an order can’t be above its own average.

A table in FROM also lets you aggregate twice. max(count(*)) isn’t allowed. Count each day’s orders in a subquery, then take the max() of those counts. Practice problem 3 does this.

Climb the ladder

All three find the customers with a backpack and no water bottle. What changes is how often each one comes back, and which orders count.

Okay

JOIN for the backpack, NOT IN for the bottle

The idea: Join each customer to their backpack orders, and drop anyone on the list of water bottle buyers.

It looks right because each backpack buyer in the store bought one.

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 8
  AND c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
  )
ORDER BY c.customer_id;

Gets right

One backpack order each, as in the store

Gets wrong

A second backpack order

Why

A JOIN gives a row for each matching order, so a second backpack gives a second row.

Where it breaks: Elif buys a second backpack, and she’s on the list twice. Okay passes 1 of the 3 checks below.

Better

IN for the backpack too

The idea: Ask, for each customer, whether they’re on the list of backpack buyers.

The gap in Okay: the JOIN gave a row for each backpack order.

Highlighted lines are new in this rung.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 8
  )
  AND customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
  )
ORDER BY customer_id;

Gets right

A second backpack orderwas One backpack order each, as in the store

Gets wrong

A cancelled order

Why

Neither subquery looks at status, so a cancelled backpack counts as bought, and so does a cancelled water bottle.

What changed from the previous rung

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 8
  AND c.customer_id NOT IN (
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 8
  )
  AND customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
  )
ORDER BY c.customer_id;
ORDER BY customer_id;

Where it breaks: Chen orders a backpack and cancels it, and still gets the offer. Grace buys a backpack and cancels a water bottle, and doesn’t get it. Better passes 2 of 3.

Best

And o.status <> 'cancelled' in each subquery

The idea: Count only the orders that weren’t cancelled, for both items.

The gap in Better: cancelled orders counted as bought.

Highlighted lines are new in this rung.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 8
      AND o.status <> 'cancelled'
  )
  AND customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
      AND o.status <> 'cancelled'
  )
ORDER BY customer_id;

Gets right

Every checkwas A second backpack order

Gets wrong

Nothing the checks try

Why

Each subquery has its own WHERE. The outer query reads customers, which have no status, so the check goes inside both.

What changed from the previous rung

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 8
      AND o.status <> 'cancelled'
  )
  AND customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items i ON i.order_id = o.order_id
    WHERE i.product_id = 4
      AND o.status <> 'cancelled'
  )
ORDER BY customer_id;

When Best isn’t best: the two subqueries read orders twice. One pass works too: join customers to their orders and items once, leave out cancelled orders with WHERE o.status <> 'cancelled', GROUP BY the customer, and keep HAVING count(*) FILTER (WHERE i.product_id = 8) > 0 AND count(*) FILTER (WHERE i.product_id = 4) = 0. It also grows well to “bought A and B, but never C”.

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.

The bottle offer

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)

-- Customers who bought a backpack (product 8) but never a water bottle (product 4). SELECT o.customer_id, i.product_id, o.status FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.product_id IN (4, 8);

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: Elif buys a second backpack (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 5, '2026-10-21', 'placed', 45.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 8, 1, 45.00);

3. ○Not run yet: Grace buys a backpack and cancels a water bottle; Chen orders a backpack and cancels it (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 7, '2026-10-21', 'placed', 45.00),
          (116, 7, '2026-10-21', 'cancelled', 12.00),
          (117, 3, '2026-10-21', 'cancelled', 45.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 8, 1, 45.00),
          (116, 4, 1, 12.00),
          (117, 8, 1, 45.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: JOIN and NOT IN	One backpack order each	A second backpack order	You need the joined table’s columns, and each row has one match
Better: IN and NOT IN	A second backpack order	A cancelled order	No order is ever cancelled
Best: and o.status <> 'cancelled' in each	Every check	Nothing the checks try	Each subquery needs the question’s rules too

Common mistakes and edge cases

!The outer WHERE doesn’t reach into a subquery

A subquery is a whole query of its own. A filter in the outer query, like “not cancelled”, doesn’t apply inside it, so write it in each subquery that needs it. Practice problem 3 shows what happens when one side forgets.

• = needs exactly one row. customer_id = (SELECT customer_id FROM customers WHERE city = 'Lisbon') works while one customer lives in Lisbon. With none, the subquery gives NULL, and nothing matches. With two, Postgres stops: “more than one row returned by a subquery used as an expression”. Use IN for a list. Practice problem 1.
• A NULL on the list empties NOT IN. x NOT IN (1, NULL) is never true, so the outer query keeps nothing. Leave the NULLs out of the list with IS NOT NULL, as in the NULL lesson.
• A column the subquery doesn’t have comes from outside. WHERE customer_id IN (SELECT customer_id FROM products) runs, though products has no customer_id. Postgres takes the outer customers’ customer_id, so every customer matches. Name the table, like p.customer_id, and the typo stops with an error instead.
• One aggregate can’t go inside another. max(count(*)) stops with “aggregate function calls cannot be nested”. Count in a subquery in FROM, then take the max() of that. Practice problem 3.
• A value from a subquery forgets where it came from. revenue IN (SELECT max(revenue) FROM sales GROUP BY month) keeps a row equal to any month’s top, even another month’s. Compare the pair instead: (month, revenue) IN (SELECT month, max(revenue) …). Practice problem 2.
• ORDER BY inside a subquery doesn’t order the result. Only the outer query’s ORDER BY does.

Say it in the interview

Questions to ask first

• Does a cancelled order count?
• Should a customer with two matches show up once or twice?
• Can the list I compare against have a NULL?

A 60-second walkthrough

“I want each customer once, so I read customers and filter with two subqueries, not a join. The first lists the customers with a backpack order that wasn’t cancelled, and IN keeps the customers on it. The second lists the ones with a water bottle order that wasn’t cancelled, and NOT IN drops them. orders.customer_id can’t be NULL, so NOT IN is safe. Then I sort by customer_id.”

Likely follow-ups

• Which orders are above the customer’s own average? Average each customer’s orders in a subquery in FROM, join it back on customer_id, and keep o.total > a.avg_total.
• IN or JOIN? IN when you only filter: each row comes back once. A JOIN when you need the other table’s columns, and then watch for repeated rows.
• What does a subquery give when it finds nothing? Used as one value, NULL. Used as a list, an empty one: IN keeps no row, and NOT IN keeps every row.

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. Orders to Lisbon (easy)

Couriers in Lisbon are on strike. Ops wants to warn every customer in Lisbon with an open order, placed or shipped. List those orders: order_id and customer_id, by order_id.

Hint 1 of 3

Don’t type Hugo’s customer_id in. Look it up with a subquery, so the answer follows the customers table.

Hint 2 of 3

= (SELECT …) works only while the subquery gives one row. With two customers in Lisbon, it stops with an error.

Hint 3 of 3

customer_id IN (SELECT customer_id FROM customers WHERE city = …) takes a list of any length, an empty one too.

Orders to Lisbon

Ctrl + Enter runs

The tables

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

SELECT order_id, customer_id, status FROM orders WHERE status IN ('placed', 'shipped');

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

1. ○Not run yet: The store’s data: Hugo is the one customer in Lisbon (row order matters)
2. ○Not run yet: Hugo moves to Porto, so nobody lives in Lisbon (row order matters)
   What this check changes

   UPDATE customers SET city = 'Porto' WHERE customer_id = 8;

3. ○Not run yet: Iris, who lives in Lisbon, signs up and orders headphones (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Iris Costa', 'iris@example.com', 'Lisbon', '2026-10-21');
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 9, '2026-10-21', 'placed', 59.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 7, 1, 59.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

customer_id = 8

The idea: Look up who lives in Lisbon by hand, and use their id.

Okay passes 1 of 3. When Hugo moves to Porto, it still lists his order.

SELECT order_id, customer_id
FROM orders
WHERE customer_id = 8
  AND status IN ('placed', 'shipped')
ORDER BY order_id;

Gets right

Hugo as the one customer in Lisbon, as in the store

Gets wrong

Anyone moving in or out

Why

8 is the answer from the day you looked. It doesn’t change when the customers do.

Better

= (SELECT customer_id …)

The idea: Look the customer up each time the query runs.

The gap in Okay: a typed-in id goes out of date. When Hugo moves away, the subquery finds no one and gives NULL, so = matches nothing, which is right.

Highlighted lines are new in this rung.

SELECT order_id, customer_id
FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'Lisbon')
  AND status IN ('placed', 'shipped')
ORDER BY order_id;

Gets right

Hugo moving away

Gets wrong

A second customer in Lisbon

Why

= compares with one value. With two rows, Postgres stops: more than one row returned by a subquery used as an expression.

What changed from the previous rung

SELECT order_id, customer_id
FROM orders
WHERE customer_id = 8
WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'Lisbon')
  AND status IN ('placed', 'shipped')
ORDER BY order_id;

Where it breaks: Iris, who lives in Lisbon, signs up and orders headphones. Now two customers live there, and Better stops with that error. Better passes 2 of 3.

Best

IN (SELECT customer_id …)

The idea: Keep the orders of anyone on the list of Lisbon customers.

The gap in Better: = takes one value, not a list.

Highlighted lines are new in this rung.

SELECT order_id, customer_id
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Lisbon')
  AND status IN ('placed', 'shipped')
ORDER BY order_id;

Gets right

Every checkwas Hugo moving away

Gets wrong

Nothing the checks try

Why

IN takes a list of any length: none, one or many customers in Lisbon.

What changed from the previous rung

SELECT order_id, customer_id
FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'Lisbon')
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Lisbon')
  AND status IN ('placed', 'shipped')
ORDER BY order_id;

When Best isn’t best: JOIN customers c ON c.customer_id = o.customer_id WHERE c.city = 'Lisbon' gives the same rows. Each order has one customer, so the join can’t repeat an order. Use it when you also want the customer’s name.

2. The top category each month (medium)

The monthly newsletter names the category that sold the most that month. List the top category for each month, all of them if there’s a tie: month, category and revenue, by month, then category.

The starter already adds up each category’s sales for each month, cancelled orders left out, in a WITH called sales. WITH names a query, so you can use it like a table, here more than once.

Hint 1 of 3

DISTINCT ON (month) keeps one row for each month, so a tie loses a category.

Hint 2 of 3

Compare with each month’s top: revenue IN (SELECT max(revenue) FROM sales GROUP BY month). But what if one month’s top equals another month’s second place?

Hint 3 of 3

Match the month too: (month, revenue) IN (SELECT month, max(revenue) FROM sales GROUP BY month).

The top category each month

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)

WITH sales AS ( SELECT extract(month FROM o.order_date) AS month, p.category, sum(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status <> 'cancelled' GROUP BY extract(month FROM o.order_date), p.category ) SELECT * FROM sales ORDER BY month, revenue DESC;

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

1. ○Not run yet: The store’s data: electronics, in September and October (row order matters)
2. ○Not run yet: In November, Chen buys two wireless mice and Grace three water bottles: 36.00 each (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-11-02', 'placed', 36.00),
          (116, 7, '2026-11-03', 'placed', 36.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 5, 2, 18.00),
          (116, 4, 3, 12.00);

3. ○Not run yet: In November, Dana buys a backpack and Elif ten notebooks: 45.00 each, what bags made in September (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-11-02', 'placed', 45.00),
          (116, 5, '2026-11-03', 'placed', 45.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 8, 1, 45.00),
          (116, 1, 10, 4.50);

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

DISTINCT ON (month)

The idea: Sort each month by revenue, and keep its first row.

Okay passes 1 of 3. In November, Chen buys two wireless mice and Grace three water bottles, 36.00 each: electronics and home tie, and Okay names only one.

WITH sales AS (
  SELECT extract(month FROM o.order_date) AS month, p.category,
    sum(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY extract(month FROM o.order_date), p.category
)
SELECT DISTINCT ON (month) month, category, revenue
FROM sales
ORDER BY month, revenue DESC;

Gets right

One top category a month, as in the store

Gets wrong

Two categories tied for the top

Why

DISTINCT ON keeps exactly one row for each month, so a tie loses one.

Better

revenue IN (SELECT max(revenue) …)

The idea: Keep every row whose revenue is a month’s top.

The gap in Okay: a tie lost a category.

Highlighted lines are new in this rung.

WITH sales AS (
  SELECT extract(month FROM o.order_date) AS month, p.category,
    sum(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY extract(month FROM o.order_date), p.category
)
SELECT month, category, revenue
FROM sales
WHERE revenue IN (SELECT max(revenue) FROM sales GROUP BY month)
ORDER BY month, category;

Gets right

A tie

Gets wrong

One month’s top equal to another month’s sales

Why

The list holds only amounts. It doesn’t say which month each top came from.

What changed from the previous rung

WITH sales AS (
  SELECT extract(month FROM o.order_date) AS month, p.category,
    sum(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY extract(month FROM o.order_date), p.category
)
SELECT DISTINCT ON (month) month, category, revenue
SELECT month, category, revenue
FROM sales
ORDER BY month, revenue DESC;
WHERE revenue IN (SELECT max(revenue) FROM sales GROUP BY month)
ORDER BY month, category;

Where it breaks: in November, Dana buys a backpack and Elif ten notebooks, so bags and stationery tie at 45.00. Bags made 45.00 in September too, so Better lists September’s bags as a top category. Better passes 2 of 3.

Best

(month, revenue) IN (SELECT month, max(revenue) …)

The idea: Compare the month and the revenue together.

The gap in Better: the list forgot which month each top came from.

Highlighted lines are new in this rung.

WITH sales AS (
  SELECT extract(month FROM o.order_date) AS month, p.category,
    sum(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY extract(month FROM o.order_date), p.category
)
SELECT month, category, revenue
FROM sales
WHERE (month, revenue) IN (SELECT month, max(revenue) FROM sales GROUP BY month)
ORDER BY month, category;

Gets right

Every checkwas A tie

Gets wrong

Nothing the checks try

Why

Each pair on the list is a month and its top, so a row matches only its own month’s top.

What changed from the previous rung

WITH sales AS (
  SELECT extract(month FROM o.order_date) AS month, p.category,
    sum(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY extract(month FROM o.order_date), p.category
)
SELECT month, category, revenue
FROM sales
WHERE revenue IN (SELECT max(revenue) FROM sales GROUP BY month)
WHERE (month, revenue) IN (SELECT month, max(revenue) FROM sales GROUP BY month)
ORDER BY month, category;

When Best isn’t best: window functions, later on the roadmap, do it in one pass over sales: rank() OVER (PARTITION BY month ORDER BY revenue DESC), then keep rank 1.

3. The busiest day (medium)

The warehouse plans its staff around the busiest day so far. List the day or days with the most orders that weren’t cancelled, and how many: order_date and orders, by order_date.

Hint 1 of 3

ORDER BY orders DESC LIMIT 1 keeps one day, even when two days tie.

Hint 2 of 3

max(count(*)) isn’t allowed. Count each day in a subquery in FROM, then take the max: (SELECT max(n) FROM (SELECT count(*) AS n FROM orders GROUP BY order_date) AS per_day).

Hint 3 of 3

The outer WHERE status <> 'cancelled' doesn’t reach the subquery. Add it inside too.

The busiest day

Ctrl + Enter runs

The tables

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

SELECT order_date, count(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY order_date;

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

1. ○Not run yet: The store’s data: 14 October, with Ben’s two orders (row order matters)
2. ○Not run yet: Chen places two orders on 21 October (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-10-21', 'placed', 4.50),
          (116, 3, '2026-10-21', 'placed', 9.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 1, 1, 4.50),
          (116, 6, 1, 9.99);

3. ○Not run yet: Chen places two orders on 21 October, and Elif three on 22 October, cancelling two (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-10-21', 'placed', 4.50),
          (116, 3, '2026-10-21', 'placed', 9.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 1, 1, 4.50),
          (116, 6, 1, 9.99);
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (117, 5, '2026-10-22', 'cancelled', 18.00),
          (118, 5, '2026-10-22', 'cancelled', 18.00),
          (119, 5, '2026-10-22', 'placed', 18.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (117, 5, 1, 18.00),
          (118, 5, 1, 18.00),
          (119, 5, 1, 18.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

ORDER BY orders DESC LIMIT 1

The idea: Sort the days by their count, and keep the first.

Okay passes 1 of 3. When Chen places two orders on 21 October, that ties with 14 October, and Okay shows only one of them.

SELECT order_date, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY order_date
ORDER BY orders DESC
LIMIT 1;

Gets right

One busiest day, as in the store: 14 October, with 2

Gets wrong

Two days tied

Why

LIMIT 1 keeps one row, whatever ties with it.

Better

HAVING count(*) = (SELECT max(n) …)

The idea: Keep every day whose count is the top count.

The gap in Okay: a tie lost a day.

Highlighted lines are new in this rung.

SELECT order_date, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY order_date
HAVING count(*) = (
  SELECT max(n)
  FROM (SELECT count(*) AS n FROM orders GROUP BY order_date) AS per_day
)
ORDER BY order_date;

Gets right

A tie

Gets wrong

A day with cancelled orders

Why

The subquery counts every order, cancelled ones too. The outer WHERE doesn’t reach it.

What changed from the previous rung

SELECT order_date, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY order_date
ORDER BY orders DESC
LIMIT 1;
HAVING count(*) = (
  SELECT max(n)
  FROM (SELECT count(*) AS n FROM orders GROUP BY order_date) AS per_day
)
ORDER BY order_date;

Where it breaks: on 22 October, Elif places three orders and cancels two. The subquery’s top count is 3, which no day reaches once cancelled orders are left out, so Better shows no day at all. Better passes 2 of 3.

Best

And WHERE status <> 'cancelled' inside

The idea: Leave out cancelled orders in both counts.

The gap in Better: the two counts didn’t count the same orders.

Highlighted lines are new in this rung.

SELECT order_date, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY order_date
HAVING count(*) = (
  SELECT max(n)
  FROM (
    SELECT count(*) AS n
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY order_date
  ) AS per_day
)
ORDER BY order_date;

Gets right

Every checkwas A tie

Gets wrong

Nothing the checks try

Why

Both counts leave out the same orders, so the top is a count some day really has.

What changed from the previous rung

SELECT order_date, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY order_date
HAVING count(*) = (
  SELECT max(n)
  FROM (SELECT count(*) AS n FROM orders GROUP BY order_date) AS per_day
  FROM (
    SELECT count(*) AS n
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY order_date
  ) AS per_day
)
ORDER BY order_date;

When Best isn’t best: HAVING count(*) >= ALL (SELECT count(*) FROM orders WHERE status <> 'cancelled' GROUP BY order_date) says the same with no subquery in FROM: at least as many as every day.

Check yourself

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

1. Predict the outputAsha (customer 1) and a new customer, Kiran, both live in Pune. What happens?

   SELECT order_id
   FROM orders
   WHERE customer_id = (SELECT customer_id FROM customers WHERE city = 'Pune');

   Asha’s orders onlyIt stops with an errorBoth customers’ ordersNo rows

   Check answer
2. Predict the outputElif (customer 5) has two orders with a backpack (product 8), and no other customer has one. How many rows?

   SELECT name
   FROM customers
   WHERE customer_id IN (
     SELECT o.customer_id
     FROM orders o
     JOIN order_items i ON i.order_id = o.order_id
     WHERE i.product_id = 8
   );

   0218

   Check answer
3. Spot the bugThis should list customers with an order. The products table has no customer_id column. What happens?

   SELECT name
   FROM customers
   WHERE customer_id IN (SELECT customer_id FROM products);

   All 8 customers come backIt stops with an errorNo rowsOnly customers with an order

   Check answer
4. Predict the outputNo customer lives in Porto. How many products does this give?

   SELECT name
   FROM products
   WHERE price > (
     SELECT max(o.total)
     FROM orders o
     JOIN customers c ON c.customer_id = o.customer_id
     WHERE c.city = 'Porto'
   );

   8, every product1, the most expensiveIt stops with an error0

   Check answer
5. Multiple choiceWhich gives the highest number of orders on any one day?

   SELECT max(count(*)) FROM orders GROUP BY order_dateSELECT count(*) FROM orders WHERE count(*) = max(count(*))SELECT max(n) FROM (SELECT count(*) AS n FROM orders GROUP BY order_date) AS per_daySELECT count(max(order_date)) FROM orders

   Check answer

Flashcards

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

• Card 1What can a subquery’s answer be?Show answer
• Card 2What does a subquery used as one value give with no rows, or with two?Show answer
• Card 3IN (SELECT …) or a JOIN, to keep the customers who bought something?Show answer
• Card 4Does the outer query’s WHERE apply inside a subquery?Show answer
• Card 5Why name a subquery in FROM, like AS per_day?Show answer
• Card 6How do you match a group’s top value with IN, without matching another group’s?Show answer

Add 6 cards to my review deck

Cheat sheet

• A subquery runs first. Its answer is one value, a list for IN, or a table in FROM.
• Used as one value, it must give one row: none gives NULL, two or more stop with an error.
• IN keeps each row once. A JOIN repeats a row for each match.
• A NULL on the list makes NOT IN keep nothing.
• The outer WHERE doesn’t reach into a subquery: put each filter where it’s needed.
• A subquery in FROM is a table: name it, and use it to aggregate twice.

Go deeper

• In the PostgreSQL docs: subquery expressions, like IN, NOT IN and ALL, scalar subqueries, and subqueries in FROM.
• Try your own orders: the bottle offer on the SQL visualizer, or any query of your own, step by step. “Show the steps” under each editor runs your own query step by step too.
• Next on the SQL roadmap: the whole map, with correlated subqueries and WITH in the core lessons.

Can you do these now?

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

• Use a subquery as one value, as a list for IN, or as a table in FROM
• Pick IN over a JOIN to keep each row once
• Put each filter in the query that needs it, inside or outside
• Say what a subquery gives when it finds no row, or more than one

✓Mark as completeI already know this

Status: Not started