Debugging a Query That Fails Because of Execution Order

1. Why WHERE fails and ORDER BY would succeed

SQL has a written order (the order clauses are typed: SELECT, FROM, WHERE, ORDER BY) and a separate logical execution order that determines what each clause can actually reference: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. recent_flag is defined inside the SELECT clause, which is step 5 in that logical order. WHERE is step 2 — it runs before SELECT has computed anything, so at the point WHERE is evaluated, recent_flag simply does not exist yet as far as the engine is concerned; it hasn't been computed, and the WHERE clause has no column list from SELECT to resolve the name against. ORDER BY, by contrast, is step 7 — it runs after SELECT (step 5) has already produced the full output column list including aliases, so by the time ORDER BY is evaluated, recent_flag is a real, already-computed value it can freely sort on. The rule isn't "WHERE can't see aliases and ORDER BY can" as an arbitrary fact to memorize — it falls directly out of which clause runs before SELECT and which runs after it.

2. The corrected query

Repeat the underlying expression in WHERE instead of referencing the alias, since the alias doesn't exist yet at that point in the pipeline:

SELECT
  customer_id,
  order_date,
  CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days'
       THEN 1 ELSE 0 END AS recent_flag
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY recent_flag DESC;

Alternatively, wrap the original query in a subquery or CTE so the alias is fully materialized before it's filtered on — useful when the underlying expression is long or reused in several places:

WITH flagged AS (
  SELECT
    customer_id,
    order_date,
    CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days'
         THEN 1 ELSE 0 END AS recent_flag
  FROM orders
)
SELECT *
FROM flagged
WHERE recent_flag = 1
ORDER BY recent_flag DESC;

Both are correct; the CTE version is preferable once the expression gets complex enough that repeating it in WHERE would hurt readability or invite a copy-paste mismatch between the two copies.

3. Does GROUP BY behave like WHERE or like ORDER BY here?

It's genuinely in between, and the honest answer names that rather than picking a side. GROUP BY (step 3) still runs before SELECT (step 5) in the strict logical order, so by the strict reading it shouldn't be able to see a SELECT alias any more than WHERE can. In practice, several major databases — PostgreSQL, MySQL, BigQuery — add a documented convenience relaxation that lets GROUP BY reference a SELECT alias anyway, resolving it back to the underlying expression on the engine's behalf. This is a database-specific ergonomic exception layered on top of the logical order, not evidence that the logical order is wrong or that GROUP BY "really" runs after SELECT. The practical guidance: it's safe to rely on in PostgreSQL/MySQL/BigQuery-style engines, but a candidate should say explicitly "this works because of a database-specific relaxation, not because of the logical order itself" — and should not assume it's portable to every SQL engine without checking, since a stricter one may reject it exactly like WHERE does.