6. Lighthouse

Level 5 · Why does this query fail?

From Tyler Morgan, Junior Data Engineer

“Hey, my query keeps erroring and I can't see why. I'm trying to get channels with more than 3,000 orders. Can you take a look?”

Your goal

Pick the explanation for why Tyler's query fails.

Tyler runs SELECT channel, COUNT(*) AS orders FROM orders WHERE COUNT(*) > 3000 GROUP BY channel; and gets an error. Why?

Pick one answer, then check it.

The lesson

SQL is written in one order and evaluated in another. Knowing the evaluation order turns confusing error messages into obvious fixes.

Written order vs logical order

You write:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

The database logically evaluates:

FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY → LIMIT

Real engines reorder work internally for speed, but they must return the same result as if they followed this logical order. So you can reason with it safely.

Tyler’s query

SELECT channel, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) > 3000
GROUP BY channel;

At the WHERE step the database is looking at one order at a time. There are no groups yet, so “the count” of what? Postgres answers with aggregate functions are not allowed in WHERE, and DuckDB, MySQL and BigQuery give similar errors. The fix:

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel
HAVING COUNT(*) > 3000;

What the order explains

question answer from the order
Can WHERE use an aggregate? No, aggregates come later.
Can HAVING use an aggregate? Yes, they exist by then.
Can WHERE use a SELECT alias? Not in standard SQL, SELECT comes later.
Can ORDER BY use a SELECT alias? Yes, it runs after SELECT.
Does LIMIT apply before or after sorting? After.

As noted earlier, DuckDB, MySQL and BigQuery relax the alias rule in some clauses. Postgres follows the standard closely, which is why “works on my laptop, fails in production” stories often involve an alias in WHERE or HAVING.

Where window functions fit

Window functions (chapter 8) are evaluated at the SELECT step, after HAVING. That is why you cannot filter on ROW_NUMBER() in WHERE either, and why “top N per group” needs a subquery or a CTE. Some warehouses (Snowflake, BigQuery, DuckDB) add a QUALIFY clause for exactly this purpose.

Interview note

Being able to recite the logical order and use it to explain an error is a strong signal. When a query errors in a live interview, say out loud which step the failing expression belongs to. Interviewers care more about that reasoning than about memorized syntax.