Level 4 · Our most loyal customers

WHERE filters rows before they are grouped. HAVING filters groups after aggregation. This query uses both:

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'      -- which orders count
GROUP BY customer_id
HAVING COUNT(*) >= 30;          -- which customers qualify

Logical order of a query

The database does not run your clauses in the order you write them. Logically it goes:

1. FROM (and joins)
2. WHERE, on individual rows
3. GROUP BY
4. aggregates are computed
5. HAVING, on groups
6. SELECT, including aliases
7. ORDER BY
8. LIMIT

This explains three rules at once:

• You cannot use COUNT(*) in WHERE, because counts do not exist yet at step 2.
• HAVING can use aggregates, because they exist by step 5.
• Aliases from SELECT are usually not visible in WHERE or HAVING, but are in ORDER BY. Postgres requires HAVING COUNT(*) >= 30, not HAVING completed_orders >= 30. DuckDB, MySQL and BigQuery accept the alias, so repeat the aggregate if you want portable SQL.

Put row conditions in WHERE

You could write the status filter in HAVING with conditional aggregation, but a plain row condition belongs in WHERE. It is clearer, and it removes rows before the expensive grouping step. A condition on a grouped column, such as HAVING channel = 'app', works but is better written as WHERE channel = 'app'. Use HAVING only for conditions that need an aggregate.

WHERE changes what HAVING sees

These two questions sound alike and give different answers:

• “Customers with 30+ orders, counting only completed ones” (WHERE status, then HAVING count).
• “Customers with 30+ orders of any status, showing their completed count” (no WHERE; the condition counts all orders, the column counts only completed).

Read the ticket twice and decide which filter applies before or after grouping. Megan asked for the first one: cancelled and refunded orders do not count toward loyalty.

Interview note

“What is the difference between WHERE and HAVING?” is asked in almost every analyst interview. A strong answer is one sentence plus a reason: WHERE filters rows before grouping, HAVING filters groups after aggregation, because aggregates do not exist until the grouping step. Mentioning that WHERE is also cheaper, since it shrinks the data before grouping, shows you think about performance.