Where does the WINDOW clause appear in a SELECT statement, and what is a named window's scope?
answer
- it is a fixed slot, not a free choice
- the names are used before they are written
- one of the last clauses in the statement
- each CTE or subquery needs its own copy
basics
~20 sThe WINDOW clause sits between HAVING and ORDER BY. A named window is scoped to the query block that defines it, and within that block OVER w can be used in the SELECT list and in the query's ORDER BY.
solid answer
~50 sTextually the clause goes near the end of the statement: `FROM ... WHERE ... GROUP BY ... HAVING ... WINDOW w AS (...) ORDER BY ...`. Writing it before `FROM` or after `ORDER BY` is a syntax error, and the position surprises people because the names it binds are used earlier in the text, in the SELECT list. That is fine — the whole query block is parsed before anything is evaluated, so a forward reference from the SELECT list to a name defined later is normal. Scope is one query block: a derived table, a CTE body, or each branch of a `UNION` is a separate block and needs its own `WINDOW` clause. Within a block the name is usable wherever a window function may appear, which is the SELECT list and the query's `ORDER BY`. Defining a window you never reference is permitted.
go deeper
Recall the slot: after HAVING, before ORDER BY. Knowing that the definition legitimately appears below the SELECT list that uses it is enough to stop you moving it when a query looks odd.
Explain that scope is a single query block and demonstrate it — a CTE body and its consumer each need their own clause. Be ready to say that the clause binds names only and adds no evaluation stage.
Show you anticipate the consequence when refactoring: splitting a query into stacked CTEs duplicates window definitions because the language offers no cross-block sharing, and that duplication is expected rather than a smell to engineer around.
Worth owning is the limit itself: because window definitions cannot be shared across query blocks or statements, standardising analytical windows across a codebase is a code-generation or view-design problem, not something the WINDOW clause can solve.
## Textual position Standard SQL fixes the clause order of a query specification, and `WINDOW` has a slot in it: ```sql SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... WINDOW w AS (PARTITION BY dept ORDER BY day) ORDER BY ... FETCH FIRST 10 ROWS ONLY ``` It comes after `HAVING` and before `ORDER BY`. There is no alternative placement: putting it next to `FROM`, or after `ORDER BY`, is a syntax error rather than a style choice. The placement looks backwards at first, because the names are *used* in the SELECT list, which is written first. SQL is comfortable with this — the entire query block is parsed as a unit, so a `RANK() OVER w` on line 3 can refer to a `WINDOW w AS (...)` on line 12. It is the same situation as `SELECT dept FROM employees`, where the SELECT list names a column belonging to a table introduced further down. Only *inside* the `WINDOW` list itself is order significant: one entry may reference an entry defined earlier in the list, never a later one. ## The textual slot is not an evaluation step A related confusion is worth heading off. The clause sitting between `HAVING` and `ORDER BY` in the text does not add a stage to the query's logical pipeline, and it does not decide when window functions are computed. It merely binds names. Window functions themselves are evaluated where they always are, on the rows that survive filtering and grouping, and naming their window changes nothing about that. ## Scope is one query block A window name is local to the query specification that defines it. Every construct that introduces a new query block introduces a new namespace: ```sql WITH ranked AS ( SELECT emp_id, dept, salary, RANK() OVER w AS r FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC) ) SELECT dept, COUNT(*) OVER w2 AS c -- w is NOT visible here FROM ranked WHERE r <= 3 WINDOW w2 AS (PARTITION BY dept); ``` The outer query cannot see `w`; it needs its own clause. The same holds for a derived table in `FROM`, for a scalar subquery in the SELECT list, and for each arm of a `UNION`, `INTERSECT` or `EXCEPT` — each arm is a separate query specification with its own optional `WINDOW` clause. There is no statement-level or session-level window namespace, and no way to define a window once for a whole script; the name vanishes when the query block ends. ## Where a name may be used Within its block, `OVER w` is legal in exactly the places a window function is legal: the SELECT list, and the query's `ORDER BY`. So this is well-formed: ```sql SELECT emp_id, salary FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC) ORDER BY RANK() OVER w, emp_id; ``` It is not legal in `WHERE` or `HAVING` — but that restriction belongs to window functions in general, not to named windows; naming a window neither grants nor removes any placement freedom. ## Unused definitions and duplicate names A `WINDOW` entry that nothing references is not an error; it is dead code, and worth deleting for the same reason an unused variable is. Two entries with the same name in one clause, on the other hand, are a definition conflict and are rejected. ## Practical consequence Because the scope is a single block, refactoring a long query into stacked CTEs will sometimes force you to repeat a `WINDOW` clause in two blocks. That is expected, and it is not a sign you have done something wrong — the alternative, a shared window definition spanning blocks, simply does not exist in the language.
- How can the SELECT list use a name the WINDOW clause defines further down?Because the whole query block is parsed before evaluation begins, so the reference is resolved against the block's complete text, not line by line. It is the same reason the SELECT list can name a column from a table that only appears in the `FROM` clause below it. Ordering only matters inside the `WINDOW` list itself, where an entry may reference earlier entries only.
- Can a CTE's named window be referenced by the outer query that selects from it?No. Scope is one query block, and a CTE body is its own block. The outer query must declare its own `WINDOW` clause if it needs one, even to define an identical window. The same applies to derived tables and to each branch of a set operation.
- Is it an error to define a named window and never reference it?No, an unreferenced entry is permitted and simply unused, though it is worth deleting as dead code. Duplicate names within one `WINDOW` clause are a different matter and are rejected as a definition conflict.
saying these in an interview costs you the question
- Places the WINDOW clause immediately after FROM
- Thinks the SELECT list cannot reference a name defined later
- Believes a CTE's named window is visible to the outer query
- Expects a named window to persist across statements or sessions
- Treats the clause's position as an extra evaluation stage