Why Window Functions Cannot Go Directly in WHERE

A common beginner question is: “If window functions run late, can I filter on them in `WHERE`?”

Usually, no. `WHERE` happens earlier than window functions in SQL's logical order.

So a query like this is conceptually backwards:

SELECT salesperson, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales
WHERE region_rank = 1

At the moment `WHERE` runs, `region_rank` does not exist yet.

The usual fix is to compute the window function in a subquery or CTE first, then filter the outer query:

WITH ranked_sales AS (
  SELECT region, salesperson, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
  FROM sales
)
SELECT region, salesperson, amount
FROM ranked_sales
WHERE region_rank = 1

That works because the outer query's `WHERE` sees `region_rank` as a normal column from the inner query result.

DuckDB also supports `QUALIFY`, which filters after window functions. A `QUALIFY region_rank = 1` clause can be a concise alternative to the subquery pattern.