Dana first ordered in August and got September’s welcome email

See the problem

Each month, the shop emails a welcome gift to its new customers: people whose first order was that month. September’s list came from this query:

September’s welcome list

SELECT customer_id, min(order_date) AS first_order
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
GROUP BY customer_id
ORDER BY customer_id;

It found Asha, Ben, Chen and Elif. Each of them first ordered in September, so the list was right.

Then the team moved the old shop’s orders into the database. Dana had ordered there on 20 August. She ordered again on 9 September, and the query put her on September’s list too.

Define the task

List each customer whose first order is in September 2026: customer_id and first_order, the date, by customer_id.

• A customer who ordered before September isn’t new, even if they ordered in September too.
• A cancelled order doesn’t count. If someone’s first order was cancelled, their first real order decides.

Pause and think

⏸ Pause and think

WHERE runs before GROUP BY. If WHERE keeps only September’s rows, which dates can min(order_date) still see for Dana?

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

Hints open one at a time:

Hint 1 of 3

WHERE drops Dana’s August row before the groups are made. min() can’t see a row that is already gone.

Hint 2 of 3

Test the first order after grouping: HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'.

Hint 3 of 3

Not every test belongs in HAVING. A cancelled order should be gone before min() runs, so keep status <> 'cancelled' in WHERE.

Understand the idea

You write SELECT first, but the database runs it near the end. Think of a kitchen. The order slip lists the dish first, but the cook starts by fetching what’s in the fridge. A query runs in this order:

1. FROM and JOIN: get the rows.
2. WHERE: drop single rows.
3. GROUP BY: put the rows that are left into groups.
4. HAVING: drop whole groups.
5. Window functions, like row_number(). A later lesson covers them.
6. SELECT: work out each column, and give names with AS.
7. DISTINCT: drop repeated rows.
8. ORDER BY: sort.
9. LIMIT and OFFSET: keep some rows.

★Each step sees only what the steps before it made

A step can’t use what a later step makes. So WHERE can’t see groups, or the names SELECT gives. And a test in WHERE changes what every later step sees.

This explains many errors. WHERE first_order >= '2026-09-01' stops with “column “first_order” does not exist”: SELECT hasn’t named it yet. WHERE min(order_date) >= … stops with “aggregate functions are not allowed in WHERE”: there are no groups yet. ORDER BY first_order works, because ORDER BY runs after SELECT.

It also explains wrong answers that don’t stop with an error. Okay tests the date in WHERE. That drops Dana’s August row before GROUP BY, so her group holds only her September order, and she looks new. The date test belongs in HAVING, after the groups are made. The cancelled test belongs in WHERE, before them.

The database may do the work in another order to save time, but the answer is always the one this order gives.

Watch the welcome list run on a few orders. Then type your own, like a customer whose only order is cancelled:

The welcome list, step by step

⤢ Full view

1. FROM orders
2. WHERE status <> 'cancelled'
3. GROUP BY customer_id
4. HAVING min(order_date) in September
5. SELECT customer_id, min(order_date)
6. ORDER BY customer_id

orders

customer_id	order_date	status
1	2026-09-02	delivered
1	2026-10-11	shipped
4	2026-08-20	delivered
4	2026-09-09	placed
9	2026-08-25	cancelled
9	2026-09-10	placed
6	2026-10-04	cancelled
6	2026-10-18	placed

Step 1 of 6. FROM orders reads all 8 rows. Nothing else has run yet.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, min(order_date) AS first_order
2FROM orders
3WHERE status <> 'cancelled'
4GROUP BY customer_id
5HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
6ORDER BY customer_id;

Try your own input

Orders: customer_id, order_date, status1, 2026-09-02, delivered 1, 2026-10-11, shipped 4, 2026-08-20, delivered 4, 2026-09-09, placed 9, 2026-08-25, cancelled 9, 2026-09-10, placed 6, 2026-10-04, cancelled 6, 2026-10-18, placedShow it

Climb the ladder

All three give September’s right list on the store’s data. What changes is which rows min(order_date) can see.

Okay

The date test in WHERE

The idea: Keep September’s orders, then take each customer’s earliest.

It looks right because the store’s first orders are all in September.

SELECT customer_id, min(order_date) AS first_order
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
GROUP BY customer_id
ORDER BY customer_id;

Gets right

A store with no orders before September

Gets wrong

A customer who ordered before September

Why

WHERE runs before GROUP BY, so min() sees only September’s orders. Anyone with a September order looks new.

Where it breaks: Dana’s August order comes in, and she still gets the email. Okay passes 1 of the 3 checks below.

Better

The date test in HAVING

The idea: Group all of each customer’s orders, then keep the groups whose earliest order is in September.

The gap in Okay: the date test ran before the groups were made.

Highlighted lines are new in this rung.

SELECT customer_id, min(order_date) AS first_order
FROM orders
GROUP BY customer_id
HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
ORDER BY customer_id;

Gets right

A customer who ordered before Septemberwas A store with no orders before September

Gets wrong

A first order that was cancelled

Why

With no WHERE at all, min() sees cancelled orders too.

What changed from the previous rung

SELECT customer_id, min(order_date) AS first_order
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
GROUP BY customer_id
HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
ORDER BY customer_id;

Where it breaks: Iris orders in August and cancels, then orders on 10 September. Her first real order is in September, but min() says 25 August, so Better leaves her out. Better passes 2 of 3.

Best

WHERE drops cancelled orders, HAVING tests the date

The idea: Drop what shouldn’t count before grouping, and test the group after.

The gap in Better: cancelled orders were still there when min() ran.

Highlighted lines are new in this rung.

SELECT customer_id, min(order_date) AS first_order
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
ORDER BY customer_id;

Gets right

Every checkwas A customer who ordered before September

Gets wrong

Nothing the checks try

Why

Each test is at the step it belongs to: cancelled rows go before min() runs, and the date test reads min()’s answer.

What changed from the previous rung

SELECT customer_id, min(order_date) AS first_order
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
ORDER BY customer_id;

When Best isn’t best: on a big table, GROUP BY reads every order ever placed. If the database can find each customer’s orders fast, WHERE NOT EXISTS an earlier order can be quicker. A later lesson covers EXISTS.

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. Show the steps under the editor runs your query in the order the database does.

September’s welcome list

Ctrl + Enter runs

The tables

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

-- Customers whose first order that wasn't cancelled is in September 2026. SELECT customer_id, order_date, status FROM orders ORDER BY customer_id, 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: no order before September (row order matters)
2. ○Not run yet: Dana’s August order from the old shop is moved in, and she orders again in September (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (90, 4, '2026-08-20', 'delivered', 12.00),
          (115, 4, '2026-09-09', 'placed', 4.50);

3. ○Not run yet: Dana’s August order too; and Iris cancels her August order, then orders in September (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (90, 4, '2026-08-20', 'delivered', 12.00),
          (115, 4, '2026-09-09', 'placed', 4.50);
   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Iris Costa', 'iris@example.com', 'Lisbon', '2026-08-01');
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (91, 9, '2026-08-25', 'cancelled', 59.00),
          (116, 9, '2026-09-10', 'placed', 59.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: date test in WHERE	No orders before September	A customer who ordered before September	You want the earliest order inside the month, not the first ever
Better: date test in HAVING	Orders before September	A first order that was cancelled	No order is ever cancelled
Best: WHERE for status, HAVING for the date	Every check	Nothing the checks try	Most of the time

Common mistakes and edge cases

!A SELECT name can hide a column

If SELECT names an expression total, and the table has a total column, WHERE still reads the table’s column. There’s no error, just a wrong list. ORDER BY, which runs after SELECT, reads the new total. Practice problem 1.

• WHERE can’t use a SELECT name. “column does not exist”. Repeat the expression, or filter in an outer query, where the name is a real column.
• HAVING can’t use one either in Postgres. HAVING first_order >= … stops with the same error. Write HAVING min(order_date) >= …. MySQL allows the name, which is why some people expect it.
• An aggregate in WHERE stops with an error. “aggregate functions are not allowed in WHERE”: there are no groups yet. Use HAVING.
• A window function in WHERE stops too. “window functions are not allowed in WHERE”: they run after it. Number the rows in a subquery, then filter outside.
• LIMIT counts rows after the join. LIMIT 2 on customers joined to orders keeps 2 orders, not 2 customers. Practice problem 2.
• GROUP BY a name that is also a column. Postgres lets GROUP BY use a SELECT name, but a table column with the same name wins. Practice problem 3.
• SELECT DISTINCT with ORDER BY a column you didn’t select stops. “for SELECT DISTINCT, ORDER BY expressions must appear in select list”: once rows are merged, there’s no single value to sort by.

Say it in the interview

Questions to ask first

• Does a cancelled order count as a first order?
• “New in September”: first order ever, or first order of the month?
• Can old orders from before the store’s start date come in later?

A 60-second walkthrough

“The database runs FROM, then WHERE, then GROUP BY and HAVING, and SELECT only after that. So I put each test where it belongs. Cancelled orders shouldn’t count at all, so WHERE drops them before grouping. Being new is about each customer’s whole history, so I group by customer and test min(order_date) in HAVING. I can’t write HAVING first_order there, because SELECT hasn’t named it yet. Then I sort by customer_id.”

Likely follow-ups

• Why can’t I use the alias in WHERE? WHERE runs before SELECT, so the name doesn’t exist yet. Repeat the expression, or wrap the query and filter outside.
• Is this the order the database really runs it in? It’s the order of meaning. The planner may reorder the work, like filtering early, but it always gives this order’s answer.
• Where do window functions run? After HAVING and before SELECT’s names and ORDER BY. So you can’t filter on one in WHERE.

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. Free delivery after the discount (easy)

Members get 12% off, and an order of 60.00 or more after the discount ships free. List those orders, cancelled ones left out: order_id and total, the discounted total rounded to cents, biggest first, then by order_id.

Hint 1 of 3

round(total * 0.88, 2) AS total gives the new total a name. Which total does WHERE read?

Hint 2 of 3

WHERE runs before SELECT, so its total is the table’s. Repeat the expression instead, or filter in an outer query.

Hint 3 of 3

Test the same value you show. 68.18 × 0.88 is 59.9984: under 60 before rounding, but 60.00 after.

Free delivery after the discount

Ctrl + Enter runs

The tables

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

SELECT order_id, total, round(total * 0.88, 2) AS discounted FROM orders WHERE status <> 'cancelled' ORDER BY total DESC;

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: Ben’s order 110 is now 64.00: 56.32 after the discount (row order matters)
   What this check changes

   UPDATE orders SET total = 64.00 WHERE order_id = 110;

3. ○Not run yet: Ben’s order 110 is now 68.18, exactly 60.00 after the discount, and order 109 is 64.00 (row order matters)
   What this check changes

   UPDATE orders SET total = 68.18 WHERE order_id = 110;
   UPDATE orders SET total = 64.00 WHERE order_id = 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

WHERE total >= 60

The idea: Name the discounted total total, and test total.

Okay passes 1 of 3. On the store’s data, no order is between 60.00 and 68.18, so the two totals agree by luck. When Ben’s order 110 becomes 64.00, it ships free, though it’s 56.32 after the discount.

SELECT order_id, round(total * 0.88, 2) AS total
FROM orders
WHERE status <> 'cancelled' AND total >= 60
ORDER BY total DESC, order_id;

Gets right

The store’s orders

Gets wrong

An order just over 60.00 before the discount

Why

WHERE runs before SELECT, so it reads the table’s total, before the discount.

Better

WHERE total * 0.88 >= 60

The idea: Repeat the discount in WHERE.

The gap in Okay: WHERE read the table’s total.

Highlighted lines are new in this rung.

SELECT order_id, round(total * 0.88, 2) AS total
FROM orders
WHERE status <> 'cancelled' AND total * 0.88 >= 60
ORDER BY total DESC, order_id;

Gets right

An order just over 60.00 before the discount

Gets wrong

An order that rounds up to 60.00

Why

WHERE tests the value before rounding, but the list shows it after. They differ for an order of 68.18.

What changed from the previous rung

SELECT order_id, round(total * 0.88, 2) AS total
FROM orders
WHERE status <> 'cancelled' AND total >= 60
WHERE status <> 'cancelled' AND total * 0.88 >= 60
ORDER BY total DESC, order_id;

Where it breaks: order 110 becomes 68.18. That’s 59.9984 after the discount, so Better leaves it out, but the customer sees 60.00. Better passes 2 of 3.

Best

Discount in a subquery, test outside

The idea: Work out the total once, in a subquery, and filter on its name outside.

The gap in Better: the test and the list used two different values.

Highlighted lines are new in this rung.

SELECT order_id, total
FROM (
  SELECT order_id, round(total * 0.88, 2) AS total
  FROM orders
  WHERE status <> 'cancelled'
) AS discounted
WHERE total >= 60
ORDER BY total DESC, order_id;

Gets right

Every checkwas An order just over 60.00 before the discount

Gets wrong

Nothing the checks try

Why

Outside the subquery, total is a real column: the rounded, discounted one. The test and the list use the same value.

What changed from the previous rung

SELECT order_id, round(total * 0.88, 2) AS total
FROM orders
WHERE status <> 'cancelled' AND total * 0.88 >= 60
SELECT order_id, total
FROM (
  SELECT order_id, round(total * 0.88, 2) AS total
  FROM orders
  WHERE status <> 'cancelled'
) AS discounted
WHERE total >= 60
ORDER BY total DESC, order_id;

When Best isn’t best: WHERE round(total * 0.88, 2) >= 60 gives the same rows with no subquery. It’s fine when the expression is short and written in one place.

2. The two newest customers (medium)

The welcome team calls the two customers who signed up most recently. For each, list every order: customer_id, name and order_id, newest customer first, then by order_id. A customer with no order yet still shows, with no order_id. Break a tie on sign-up day by the higher customer_id.

Hint 1 of 3

LIMIT runs last, after the join. LIMIT 2 keeps 2 joined rows: 2 orders, not 2 customers.

Hint 2 of 3

Pick the two customers first, in a subquery with its own ORDER BY and LIMIT 2. Then keep their rows with IN.

Hint 3 of 3

A JOIN drops a customer with no orders. LEFT JOIN keeps them, with NULL for order_id.

The two newest customers

Ctrl + Enter runs

The tables

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

SELECT customer_id, name, signed_up_on FROM customers ORDER BY signed_up_on DESC;

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

1. ○Not run yet: The store’s data: Hugo and Grace, one order each (row order matters)
2. ○Not run yet: Grace orders again (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', 12.00);

3. ○Not run yet: Grace orders again, and Iris signs up but hasn’t ordered yet (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', 12.00);
   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Iris Costa', 'iris@example.com', 'Lisbon', '2026-10-21');

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, ORDER BY, LIMIT 2

The idea: Join customers to orders, sort by sign-up day, and keep 2.

Okay passes 1 of 3. When Grace orders again, the join gives three rows, and LIMIT 2 drops her second order.

SELECT c.customer_id, c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.signed_up_on DESC, o.order_id
LIMIT 2;

Gets right

Hugo and Grace with one order each, as in the store

Gets wrong

A new customer with two orders

Why

LIMIT runs after the join, so it counts orders, not customers.

Better

Pick the customers first

The idea: A subquery picks the two newest customers, and IN keeps their orders.

The gap in Okay: LIMIT counted joined rows.

Highlighted lines are new in this rung.

SELECT c.customer_id, c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (
  SELECT customer_id
  FROM customers
  ORDER BY signed_up_on DESC, customer_id DESC
  LIMIT 2
)
ORDER BY c.signed_up_on DESC, o.order_id;

Gets right

A new customer with two orders

Gets wrong

A new customer with no orders

Why

An inner JOIN drops a customer with no order to match.

What changed from the previous rung

SELECT c.customer_id, c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.signed_up_on DESC, o.order_id
LIMIT 2;
WHERE c.customer_id IN (
  SELECT customer_id
  FROM customers
  ORDER BY signed_up_on DESC, customer_id DESC
  LIMIT 2
)
ORDER BY c.signed_up_on DESC, o.order_id;

Where it breaks: Iris signs up today and hasn’t ordered. She’s one of the two newest, but the JOIN drops her, so the list has only Hugo. Better passes 2 of 3.

Best

And LEFT JOIN

The idea: Keep the customers with no order too.

The gap in Better: the JOIN dropped a customer with no order.

Highlighted lines are new in this rung.

SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (
  SELECT customer_id
  FROM customers
  ORDER BY signed_up_on DESC, customer_id DESC
  LIMIT 2
)
ORDER BY c.signed_up_on DESC, o.order_id;

Gets right

Every checkwas A new customer with two orders

Gets wrong

Nothing the checks try

Why

The subquery’s LIMIT counts customers. LEFT JOIN keeps each of them, with NULL when they have no order.

What changed from the previous rung

SELECT c.customer_id, c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (
  SELECT customer_id
  FROM customers
  ORDER BY signed_up_on DESC, customer_id DESC
  LIMIT 2
)
ORDER BY c.signed_up_on DESC, o.order_id;

When Best isn’t best: to list each customer once, with their orders in one cell, group by customer and use string_agg(o.order_id::text, ', '). Then a plain LIMIT 2 counts customers.

3. Customers per city (medium)

Marketing wants a count of customers in each city. Some people typed their city in lower case, or with spaces at the ends, so count those together. List city, in lower case with no spaces at the ends, and customers, most first, then by city.

Hint 1 of 3

SELECT lower(trim(city)) AS city … GROUP BY city looks right. But which city does GROUP BY use, the table’s or the new one?

Hint 2 of 3

GROUP BY runs before SELECT. When a table column has the same name, Postgres groups by the column, so “Pune” and “pune” stay apart.

Hint 3 of 3

Group by the whole expression, lower(trim(city)), so every way of typing the city lands in one group.

Customers per city

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

SELECT city, count(*) AS customers FROM customers GROUP BY city ORDER BY customers DESC, city;

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

1. ○Not run yet: The store’s data: every city typed the same way (row order matters)
2. ○Not run yet: Kiran signs up from “pune”, in lower case (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Kiran Joshi', 'kiran@example.com', 'pune', '2026-10-21');

3. ○Not run yet: Kiran from “pune”, and Lena from “ Pune ”, with spaces (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Kiran Joshi', 'kiran@example.com', 'pune', '2026-10-21');
   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (10, 'Lena Fischer', 'lena@example.com', ' Pune ', '2026-10-22');

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

AS city, then GROUP BY city

The idea: Tidy the city in SELECT, and group by its name.

Okay passes 1 of 3. When Kiran signs up from “pune”, the list has two rows that both say pune, 1 each.

SELECT lower(trim(city)) AS city, count(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

Gets right

Every city typed the same way, as in the store

Gets wrong

“pune” beside “Pune”

Why

GROUP BY runs before SELECT. city is also a table column, so Postgres groups by the column as typed.

Better

GROUP BY lower(city)

The idea: Group by the expression, not the name.

The gap in Okay: GROUP BY used the table’s city, not the tidy one.

Highlighted lines are new in this rung.

SELECT lower(city) AS city, count(*) AS customers
FROM customers
GROUP BY lower(city)
ORDER BY customers DESC, city;

Gets right

“pune” beside “Pune”

Gets wrong

“ Pune ”, with spaces

Why

lower() fixes capitals, but not spaces at the ends.

What changed from the previous rung

SELECT lower(trim(city)) AS city, count(*) AS customers
SELECT lower(city) AS city, count(*) AS customers
FROM customers
GROUP BY city
GROUP BY lower(city)
ORDER BY customers DESC, city;

Where it breaks: Lena types “ Pune ”, and Better counts her apart from pune. Better passes 2 of 3.

Best

GROUP BY lower(trim(city))

The idea: Group by exactly the expression you show.

The gap in Better: it grouped by a different expression from the one the task asked for.

Highlighted lines are new in this rung.

SELECT lower(trim(city)) AS city, count(*) AS customers
FROM customers
GROUP BY lower(trim(city))
ORDER BY customers DESC, city;

Gets right

Every checkwas “pune” beside “Pune”

Gets wrong

Nothing the checks try

Why

The group and the shown city are the same expression, so every way of typing a city lands in one group.

What changed from the previous rung

SELECT lower(city) AS city, count(*) AS customers
SELECT lower(trim(city)) AS city, count(*) AS customers
FROM customers
GROUP BY lower(city)
GROUP BY lower(trim(city))
ORDER BY customers DESC, city;

When Best isn’t best: GROUP BY 1 means the first SELECT column, so you write the expression once. Better still, tidy cities when they’re saved, so every query sees clean data.

Check yourself

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

1. Predict the outputWhat happens when you run this?

   SELECT customer_id, sum(total) AS spent
   FROM orders
   WHERE spent >= 100
   GROUP BY customer_id;

   It lists the customers who spent 100 or moreIt lists every customerIt stops with an error: column "spent" does not existIt gives no rows

   Check answer
2. Multiple choiceIn which order does the database run these parts of a query?

   SELECT, FROM, WHERE, GROUP BY, ORDER BYFROM, WHERE, GROUP BY, SELECT, ORDER BYFROM, SELECT, WHERE, ORDER BY, GROUP BYWHERE, FROM, SELECT, GROUP BY, ORDER BY

   Check answer
3. Predict the outputAsha (customer 1) ordered on 2 September and 11 October. Does she show up?

   SELECT customer_id, min(order_date) AS first_order
   FROM orders
   WHERE order_date >= '2026-10-01'
   GROUP BY customer_id;

   Yes, with first_order 2026-10-11Yes, with first_order 2026-09-02NoIt stops with an error

   Check answer
4. Spot the bugThe discount is 10%, and the list should show orders of 60 or more after it. What’s wrong?

   SELECT order_id, round(total * 0.9, 2) AS total
   FROM orders
   WHERE total >= 60;

   NothingIt stops with an error: total is used twiceWHERE reads the table’s total, before the discountround() can’t go in SELECT

   Check answer
5. Multiple choiceWhich part of a query can use a name given with AS in SELECT?

   WHEREHAVINGORDER BYThe ON of a JOIN

   Check answer

Flashcards

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

• Card 1In what order does the database run a query’s parts?Show answer
• Card 2Why can’t WHERE use a name made in SELECT?Show answer
• Card 3WHERE or HAVING, to test a customer’s first order date?Show answer
• Card 4Your SELECT names an expression total, and the table has a total column. Which does WHERE read?Show answer
• Card 5Can GROUP BY use a SELECT name?Show answer

Add 5 cards to my review deck

Cheat sheet

• The order: FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT.
• WHERE drops rows before the groups. HAVING drops groups after.
• A name made in SELECT is seen only by ORDER BY. Elsewhere, repeat the expression, or filter in an outer query.
• If a SELECT name is also a column, WHERE and GROUP BY read the column.
• LIMIT runs last, so after a join it counts joined rows.

Go deeper

• In the PostgreSQL docs: how a SELECT is run, step by step, and where output column names can be used.
• Try your own query: any query on the SQL visualizer shows each step in this order. The welcome list takes your own orders.
• Next on the SQL roadmap: the whole map, with WITH, which names a query so long ones read top to bottom.

Can you do these now?

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

• Say the order a query runs in, from FROM to LIMIT
• Put each test where it belongs, in WHERE before the groups or in HAVING after
• Explain why WHERE can’t use a name made in SELECT, and work around it
• Spot a SELECT name that hides a column, and a LIMIT that counts joined rows

✓Mark as completeI already know this

Status: Not started