SQL, data modeling, data quality, EDA, visualization · insight-1038
Filter on a window function result in an outer query, because WHERE runs before windows
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
You want only each flight's most recent snapshot and write WHERE ROW_NUMBER() OVER (...) = 1 in the same SELECT, or you add a WHERE condition and expect the window to still see all rows.
Action
Compute the window in a subquery or CTE, then filter in the outer query (or use QUALIFY where the dialect has it). Put business filters that must not change the window's input inside the inner level, and ones that only select output rows outside.
Reason
Evaluation order is FROM, WHERE, GROUP BY, HAVING, then window functions in the select list, then DISTINCT, ORDER BY and LIMIT; a window function cannot be referenced in WHERE.
Boundary
Latest-row selection, top-N, running balances, period-over-period changes.
Symptom or risk
The engine rejects the first form; worse, a WHERE filter on the same level removes rows before the window is computed, so ranks, running totals and lags are computed on a reduced set.
Exceptions
A filter on the PARTITION BY column alone is safe to apply early and often faster.
Trade-offs
Nested queries are longer; a CTE name that states the grain keeps them readable.
Pitfalls
Filtering out cancelled flights in WHERE and then reporting a LAG-based change as if the cancelled day had never been seen.
Lessons and concepts
Claims and their sources
In PostgreSQL WHERE filters rows before grouping and windowing occurs after grouping and aggregation.