Given WINDOW w AS (PARTITION BY dept ORDER BY salary), what may OVER (w ...) add or override?
answer
- refinement only ever adds, never contradicts
- one of the three clauses can always be supplied
- one can be supplied only if the base omits it
- one may never be written at the reference site
basics
~20 sOnly a frame clause. Because w already supplies PARTITION BY and ORDER BY, a referencing window may add ROWS, RANGE or GROUPS bounds and nothing else; restating PARTITION BY or supplying a different ORDER BY is an error.
solid answer
~50 sA referencing window *copies* from the named one and may only extend it, never contradict it. Three rules cover every case. **PARTITION BY is always inherited and may never be written again** — not even the same list — so `OVER (w PARTITION BY dept)` fails. **ORDER BY may be added only if the referenced window has none**; since `w` already orders by salary, `OVER (w ORDER BY hire_date)` and even `OVER (w ORDER BY salary, emp_id)` are both errors. **The frame is never inherited and is always the referencing window's own**, so adding one is exactly what refinement is for: `OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`. The mirror-image rule matters just as much: the window you reference must not itself contain a frame clause, which is why a base window intended for refinement is written frame-free.
go deeper
You mainly need to recognise the shape: the window name comes first inside the parentheses and what follows extends it. Remember that a frame is the thing you normally add.
This is the tier where the three rules are expected verbatim: partitioning inherited and never restated, ordering addable only if absent, frame always your own and never present in the base. Be able to point at an illegal example and say which rule it breaks.
Demonstrate the design consequence — base windows are written frame-free so they remain refinable — and show you can diagnose the resulting engine error quickly by moving the offending clause out of the base rather than duplicating the whole specification.
The judgment call is how far to push named windows in shared analytics SQL: refinement chains are elegant but the rules are unfamiliar to many reviewers, and a query nobody on the team can safely edit is worse than a slightly repetitive one.
## The shape of a refinement Inside `OVER (...)` the standard allows an optional *existing window name* as the very first element, followed by the usual optional partition, order and frame clauses: ```sql OVER ( [existing_name] [PARTITION BY ...] [ORDER BY ...] [frame] ) ``` When the name is present, the new window is built from the named one. The design intent is one-directional: a refinement may make the window *more* specific, never differently specific. That single idea generates all the rules below. ## Rule 1 — PARTITION BY is inherited and may not be restated The new window always takes its partitioning from the referenced window, and it is forbidden to write a partition clause of its own. This surprises people because restating the identical list feels harmless: ```sql -- both illegal against WINDOW w AS (PARTITION BY dept ORDER BY salary) OVER (w PARTITION BY region) -- a different partitioning OVER (w PARTITION BY dept) -- the same partitioning, still rejected ``` The rule is syntactic, not semantic: the parser rejects the presence of the clause, it does not compare the lists. Partitioning is therefore the one thing you can be certain every user of `w` shares — which is precisely what makes named windows worth using in a report query. ## Rule 2 — ORDER BY may be added, never replaced If the referenced window has no ordering, the refinement may supply one. If it already has one, the refinement may not supply any ordering at all: ```sql -- w AS (PARTITION BY dept ORDER BY salary) OVER (w ORDER BY hire_date) -- illegal: w is already ordered OVER (w ORDER BY salary, emp_id) -- illegal: extending is still supplying one OVER (w ORDER BY salary) -- illegal: identical is still supplying one -- base AS (PARTITION BY dept) OVER (base ORDER BY salary DESC) -- legal: base had no ordering ``` Note the middle case. Adding a tiebreaker column to make a ranking deterministic *feels* like refinement, but the standard treats any order clause in the referencing window as an override attempt when the base is already ordered. If different columns need different orderings, they need different base windows, or an ordering left out of the base entirely. ## Rule 3 — the frame is never inherited, and the base must not have one The frame clause behaves the opposite way from the other two. A referencing window always uses its own frame, and it may always supply one — this is the main reason to refine at all. The counterpart restriction is that **the referenced window must not specify a frame clause**: ```sql -- illegal: the base already carries a frame WINDOW w AS (PARTITION BY dept ORDER BY day ROWS UNBOUNDED PRECEDING) ... SUM(amount) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ``` This is not an override that silently loses; it is an error. The practical consequence is a design rule: **a window you intend to refine is defined with partition and order only, and every frame lives at the reference site.** Put a frame in the base and you have built a leaf window that can be used with `OVER w` but never extended. When a refinement adds nothing frame-related — plain `OVER w` — the usual default frame applies: with an `ORDER BY` present, that default includes the current row and all preceding rows in the partition together with the current row's peers; with no `ORDER BY`, the frame is the whole partition. Naming the window does not change those defaults in either direction. ## Reading an error message Engines report these as syntax or feature errors rather than as wrong answers, which is a mercy: a rejected refinement never silently returns the wrong numbers. When you hit one, the fix is almost always to move something out of the base window. Ordering conflict? Take `ORDER BY` out of the base and put it on each reference, or define a second name. Frame conflict? Strip the frame from the base. Partition conflict? You need a genuinely different window, and it deserves its own name in the `WINDOW` list. ## Chained definitions obey the same rules Entries inside the `WINDOW` clause may reference earlier entries, and the rules are identical there: ```sql WINDOW w AS (PARTITION BY dept ORDER BY day), w3 AS (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ``` `w3` inherits partitioning and ordering from `w`, adds a frame, restates nothing — legal. Had `w` carried a frame, `w3` would be rejected for the same reason a reference site would be.
- Why can a refinement not add a tiebreaker column such as OVER (w ORDER BY salary, emp_id)?Because the rule is about the presence of an order clause, not about whether it is compatible. If the referenced window already orders, the referencing window may not supply any ordering, and extending the list counts as supplying one. To vary the ordering, leave `ORDER BY` out of the base window and put it on each reference, or define a second named window.
- What design rule follows from "the referenced window must not specify a frame clause"?Write base windows frame-free. Keep `PARTITION BY` and `ORDER BY` in the named window and put every frame at the reference site with `OVER (w ROWS ...)`. A base that carries a frame can still be used with plain `OVER w`, but it becomes a dead end — no reference and no later `WINDOW` entry can build on it.
- If a refinement adds no frame, which frame does the function use?The ordinary default for the resulting window. With an `ORDER BY` present the default frame runs from the start of the partition through the current row and its peers; with no `ORDER BY` it is the entire partition. Naming a window neither introduces a frame nor suppresses the default.
saying these in an interview costs you the question
- Thinks restating the identical PARTITION BY is harmless
- Believes a refinement's ORDER BY silently overrides the base
- Says a frame in the base window is inherited unless overridden
- Assumes adding a tiebreaker to the ordering counts as refining
- Expects a conflicting refinement to return wrong rows rather than error