
Read the tables before the question
Ask or check: what is one row in each table? One row per order, or one per item in an order? Most wrong answers come from joining tables without knowing that, and counting things twice.
Build it in the order the database runs it
- FROM and JOIN. Which tables, and how they connect.
- WHERE. Which rows to keep.
- GROUP BY. What one row of the result stands for.
- HAVING. Which groups to keep.
- SELECT. What to show.
- ORDER BY and LIMIT. In what order, and how many.
Writing SELECT last feels backwards, and it prevents most mistakes.
An example
-- Customers who placed more than three orders in October, with their total
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= DATE '2026-10-01'
AND o.created_at < DATE '2026-11-01'
GROUP BY c.id, c.name
HAVING COUNT(*) > 3
ORDER BY spent DESC;The five ideas
- Filtering. WHERE works before grouping, HAVING after.
- Joining. An inner join drops rows with no match. A left join keeps them, with empty values on the right.
- Grouping. Every column you show is either grouped by or inside an aggregate.
- Ranking. The top three per group needs a window function, which numbers the rows inside each group.
- Missing values. NULL is not equal to anything, including itself.
Three classic traps
- The join that multiplies. Join orders to their items and then sum the order total, and each order is counted once per item.
- The customers with no orders. An inner join silently leaves them out. If the question says all customers, you need a left join.
- NULL in a comparison. A filter for not equal to a value also drops the rows where the column is empty.
Talk while you write
Before you write anything, say what one row of your result means. Then say what each clause is for as you add it. When you finish, walk two or three rows of sample data through the query. This catches the multiplying join almost every time.
When they ask about performance
Say which columns the query filters and joins on, that you would check for an index on them, and that you would look at the query plan. At most levels, that is the answer.