Caso Abierto Español

Field manual · SQL traps

Why can't WHERE filter on a window function's result?

WHERE runs before the columns computed with OVER even exist. Filtering on a window function needs QUALIFY, or wrapping the query in a CTE and filtering from the outside.

Where it shows up

Any filter on lag(), row_number(), sum() OVER (…) or another window function: 'the previous row less than 10 minutes ago', 'only the first of each group', 'only rows ranked first'.

Example

Wrong: DuckDB rejects it, it won't even run

SELECT card, moment,
       lag(moment) OVER (PARTITION BY card ORDER BY moment) AS previous
FROM validations
WHERE date_diff('minute', lag(moment) OVER (PARTITION BY card ORDER BY moment), moment) < 10;
-- error: a window function can't be called inside WHERE

Right: QUALIFY filters after the window is computed

SELECT card, moment,
       lag(moment) OVER (PARTITION BY card ORDER BY moment) AS previous
FROM validations
QUALIFY date_diff('minute', previous, moment) < 10;

A query's real execution order is FROM → WHERE → GROUP BY → HAVING → *windows* → QUALIFY → SELECT. Window functions get computed after WHERE, so a WHERE can't reference lag(...) OVER (...): the column doesn't exist yet at that point, and DuckDB rejects it with an error, not a wrong answer. QUALIFY is exactly HAVING for windows: it runs after they're computed and can use their result directly, including the alias you gave it in the SELECT. On engines without QUALIFY (nearly all except DuckDB, Snowflake and BigQuery) the equivalent is wrapping the query in a CTE and putting the filter in the outer query's WHERE, where the computed column already exists as an ordinary column.