In SQL's logical evaluation order, when are window functions computed relative to WHERE, HAVING and LIMIT?

## The pipeline SQL defines the *meaning* of a query as a sequence of logical steps. Adding window functions, it reads: 1. `FROM` / `JOIN` — build the row source 2. `WHERE` — discard rows that fail the predicate 3. `GROUP BY` — collapse the survivors into groups 4. `HAVING` — discard groups that fail the predicate 5. **window functions** — evaluated over the rows produced by steps 1–4 6. `SELECT` list and `DISTINCT` — project, then deduplicate 7. `ORDER BY` — order the result 8. `OFFSET` / `FETCH FIRST` (or `LIMIT`) — take a slice This is a *semantic* model, not a description of how an engine physically executes anything; the engine may do the work in any order that yields the same answer. But every question about what a query *returns* is settled by this list. ## Consequence 1: filters shrink what the window sees Because `WHERE` runs at step 2 and windows at step 5, a window function never sees a filtered-out row. ```sql SELECT order_id, amount, SUM(amount) OVER () AS total_shown FROM orders WHERE status = 'PAID'; ``` `total_shown` is the total of paid orders, not of all orders. Usually that is precisely what you want — the totals describe the rows on screen. It becomes a bug only when you wanted a denominator that ignores the filter, and then the fix is to move the filter outside the window's query level (see the rewrite below). The same holds for `HAVING` in a grouped query: groups it removes are gone before the window runs, so a grand total computed with `SUM(SUM(x)) OVER ()` excludes them. ## Consequence 2: the row limit does not shrink what the window sees `ORDER BY` and the limit clause run at steps 7–8, *after* windows. ```sql SELECT order_id, amount, COUNT(*) OVER () AS matching_rows FROM orders WHERE status = 'PAID' ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY; ``` You get ten rows, and every one of them carries the count of **all** paid orders. This is a well-known and genuinely useful idiom: one statement returns both a page of data and the total needed to render "page 1 of 37", with no second `COUNT` query that could disagree with the first because the data moved in between. It also means a running total in a paged query is a running total over the whole filtered set, not restarted per page — which is what a paginated report normally wants, and a surprise if you assumed the limit applied first. ## Consequence 3: earlier clauses cannot reference a window result Since windows are evaluated at step 5, nothing at steps 2–4 can see their output. A predicate on a window value must therefore be applied at a later stage — conventionally by computing the window in an inner query (a subquery or CTE) and filtering in the outer one, where the value is an ordinary column. ```sql SELECT * FROM (SELECT order_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS cust_total FROM orders) t WHERE cust_total > 1000; ``` The inner query computes the window over all rows; the outer predicate then filters on the result. The same wrapping technique is what makes the total-ignores-the-filter case work: put the window in the inner query over the unfiltered rows and apply the predicate outside. ## Consequence 4: DISTINCT is applied after windows `SELECT DISTINCT` deduplicates the projected rows at step 6, so the window has already produced a value for every row. Adding `ROW_NUMBER()` to a `SELECT DISTINCT` list makes every projected row unique, and the `DISTINCT` then removes nothing. That is not a bug in the engine; it follows directly from the ordering. ## Where this sits relative to grouping Step 5 comes after step 3, which is why a window function in a grouped query operates on the grouped rows and may take an aggregate as its argument. And it is why the two mechanisms compose rather than compete: grouping decides how many rows exist, windowing decorates whatever rows are left. ## A checkable summary Ask two questions of any query with an `OVER` clause: - **Which rows reach the window?** Everything after `WHERE`, grouping and `HAVING`. If a predicate is in the same query level, it has already applied. - **Which clauses run after the window?** `DISTINCT`, `ORDER BY`, `OFFSET`/`FETCH`. They cannot change what the window computed; they only choose what you see of it. ## What an interviewer is checking That you can place windowing in the pipeline without hesitation, and that you can immediately derive the two practical results: filters change window values, limits do not.