Why can't a window function appear in WHERE, and how do you filter on one?

## The rule Standard SQL permits a window function in exactly two positions in a query specification: the **select list** and the query's **ORDER BY** clause. Everywhere else is an error: - `WHERE` — rejected - `GROUP BY` — rejected - `HAVING` — rejected - a join's `ON` condition — rejected - inside the argument of an aggregate function, or inside another window function — rejected The error message varies by engine ("window functions are not allowed in WHERE", "invalid use of window function", and similar) but every conforming engine refuses. ## Why the restriction exists A query's clauses have a defined order of *meaning*, independent of how the engine physically executes it. Rows come from `FROM`, are filtered by `WHERE`, grouped by `GROUP BY`, filtered again by `HAVING` — and only then are window functions evaluated over whatever rows are left, before the final `ORDER BY` and any row limit. That placement is not an accident; it is what makes window functions well-defined. A window value depends on a *set* of rows: the partition, the sequence, the frame. If `WHERE` could test a window value, the engine would have to know the window's contents in order to decide which rows belong in the window — and the contents depend on which rows the filter keeps. Removing one row can change the average, the count and every row number in the partition, which would change which rows the filter keeps, and so on. The language cuts the loop by fixing the order: filter first, then compute windows over what survived. This also explains a related error people hit: you cannot reference the window column's *alias* in `WHERE` either. `SELECT ..., AVG(x) OVER () AS a FROM t WHERE a > 5` fails, because the alias names a select-list expression that has not been evaluated at `WHERE` time. `HAVING` is not a loophole. `HAVING` filters groups after grouping but still before windows are computed, so a window function is just as illegal there. ## The fix: one more level of nesting Because each query level completes before the level above it consumes its rows, moving the window function down a level makes it an ordinary column upstairs, which `WHERE` can test freely. **Broken:** ```sql SELECT employee_id, salary FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id); -- error: window function not allowed in WHERE ``` **Fixed with a CTE:** ```sql WITH scored AS ( SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees ) SELECT employee_id, salary, dept_avg FROM scored WHERE salary > dept_avg; ``` **Fixed with a derived table** — identical in meaning, and note the mandatory alias on the subquery: ```sql SELECT employee_id, salary FROM ( SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees ) AS scored WHERE salary > dept_avg; ``` Either form is fine; pick the one your team reads more easily. The nesting is a *semantic* requirement, and expressing it does not by itself imply a second pass over the data — how the engine executes the two levels is its own business. ## What you may still filter An important nuance: filters you write in the inner query are applied *before* the window is computed, so they change the window's contents. Filters in the outer query are applied *after*, so they select rows without disturbing the values already computed. That difference is a design decision, not a detail: ```sql -- inner filter: percentages are of EU sales only SELECT region, amount, amount * 1.0 / SUM(amount) OVER () AS share FROM sales WHERE region = 'EU'; -- outer filter: percentages are of ALL sales, then EU rows are shown SELECT * FROM ( SELECT region, amount, amount * 1.0 / SUM(amount) OVER () AS share FROM sales ) AS s WHERE region = 'EU'; ``` Both queries are correct SQL and they answer different questions. Being able to state that difference is what separates a candidate who memorised the workaround from one who understands it. ## Where a window function *is* allowed besides SELECT The query's `ORDER BY` accepts one directly: `ORDER BY ROW_NUMBER() OVER (ORDER BY created_at)` is legal, because the final sort happens after windows are computed. In practice most people compute the value in the select list and sort by its alias instead, which is clearer. ## Signals of a weak answer Saying "use HAVING instead" (same stage problem), claiming the restriction is an optimiser limitation that a newer engine version lifts, or reaching for a self-join that recomputes the aggregate independently when a single nested level would do.