Where may a FILTER (WHERE …) clause legally attach — after COUNT(DISTINCT x), before OVER, or to ROW_NUMBER()?

## The grammar Standard SQL attaches the filter clause to an *aggregate function*, in this position: ``` aggregate_name ( [ DISTINCT ] argument_list ) FILTER ( WHERE predicate ) [ OVER ( window_spec ) ] ``` Everything about legality follows from that one line. ## DISTINCT stays inside Because `DISTINCT` is a qualifier on the argument list, it lives inside the parentheses and the filter clause comes after them: ```sql SELECT COUNT(DISTINCT customer_id) FILTER (WHERE status = 'paid') AS paying_customers FROM orders; ``` The two combine cleanly: the filter admits only paid rows, and `DISTINCT` then de-duplicates the `customer_id` values among them. Writing the filter inside the parentheses, or ahead of the function name, is a syntax error. ## OVER comes last When an aggregate is used as a window function, the filter clause sits between the argument list and `OVER`: ```sql SELECT order_id, SUM(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id) AS customer_paid_total FROM orders; ``` The reading is: filter this aggregate's input, then aggregate it over the window. Rows excluded by the predicate still appear in the output — a window function never removes rows — they simply do not contribute to the aggregate's value. ## Ranking and offset functions take no filter `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE()`, `LAG()`, `LEAD()`, `FIRST_VALUE()` are defined as window functions, not aggregates. They have no filter clause in the grammar, and engines reject the attempt. To restrict what they see, you have two portable options: 1. Push the predicate into the argument, when the function has one: `MAX(CASE WHEN status = 'paid' THEN amount END) OVER (…)` for an aggregate; for `LAG(x)` you can lag a `CASE` expression. 2. Restrict the rows before the function runs, with a `WHERE` in a derived table or CTE, then join the numbering back if you still need the excluded rows. The distinction is worth stating in an interview because it is a clean test of whether a candidate understands that "window function" and "aggregate used over a window" are different categories that happen to share the `OVER` clause. ## What the predicate may contain The filter predicate is evaluated per input row, at the same conceptual point as a `WHERE` predicate, so: - It may reference any column of the rows feeding the aggregate, including columns absent from the `GROUP BY` list. - It may use any row-level expression: comparisons, `IN`, `BETWEEN`, `LIKE`, `IS NULL`, boolean combinations. - It may **not** contain an aggregate function — that would nest an aggregate inside an aggregate, which the standard forbids — nor a window function. - Only TRUE admits a row; FALSE and UNKNOWN both exclude it, so NULL-valued columns behave exactly as they would in `WHERE`. ## Several filtered aggregates together Many filtered aggregates can appear in one SELECT list, each with its own predicate, and each is evaluated independently over the same group. There is no interaction between them — one aggregate's filter never affects another's input, the grouping, or the rows returned. ## Portability, again All of the above applies only where the filter clause exists at all. PostgreSQL implements it, including with `OVER` for aggregates; SQLite implements it for aggregate functions. MySQL, SQL Server and Oracle reject the syntax entirely, and there the answers to this question are moot — you write `CASE` inside the aggregate's argument, which works identically for grouped and windowed aggregates: ```sql SUM(CASE WHEN status = 'paid' THEN amount END) OVER (PARTITION BY customer_id) ``` ## Why this comes up It shows up when someone tries `ROW_NUMBER() FILTER (WHERE …)` to number only a subset of rows, gets a syntax error, and asks why. The answer — the clause belongs to aggregates, and ranking functions are not aggregates — also tells them the right fix, which is to number within a filtered derived table.