A report repeats the same PARTITION BY and ORDER BY across eight window columns with different frames — how do you restructure it?

## The shape of the problem Multi-metric reporting queries converge on the same structure: one logical ordering of rows within a group, and several measures over different slices of it. Written inline, every column repeats the shared prefix: ```sql SUM(amount) OVER (PARTITION BY dept ORDER BY day ROWS UNBOUNDED PRECEDING) AVG(amount) OVER (PARTITION BY dept ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) MAX(amount) OVER (PARTITION BY dept ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAG(amount) OVER (PARTITION BY dept ORDER BY day) ``` Two failure modes follow. The first is editing: when the report gains a `region` dimension, eight lines must change identically, and a reviewer cannot tell from the diff whether they did. The second is reading: the eye must diff long strings character by character to answer "are these really the same window?" — and the answer is sometimes no, by accident. ## The refactor Factor the shared part into a name and leave the frame at each call site: ```sql SELECT day, dept, amount, SUM(amount) OVER (w ROWS UNBOUNDED PRECEDING) AS running_total, AVG(amount) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3, LAG(amount) OVER w AS prev_day FROM sales WINDOW w AS (PARTITION BY dept ORDER BY day); ``` Now each line carries exactly the information that distinguishes it. Adding `region` to the partition is one edit that cannot be applied inconsistently. ## Why the base must be frame-free This is the rule that makes or breaks the pattern. A window used as the basis for refinement **must not specify a frame clause**; if `w` carried `ROWS UNBOUNDED PRECEDING`, then `OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)` would be rejected outright rather than overriding it. So the design rule is: partitioning and ordering go in the base, frames go at the reference sites. A tempting shortcut — putting the most common frame in the base and overriding it for the outliers — does not exist in the language. The same logic applies to `ORDER BY`, more subtly. A referencing window may supply an order clause only if the base has none, so if two of the eight columns need a *different* ordering, they cannot refine `w`; they need a second named window. Judgment call: if most columns share the ordering, keep it in `w` and give the outliers their own name; if the orderings are all over the place, put only `PARTITION BY` in the base and let each column order itself. ## Naming shared frame combinations When three columns want the same non-default frame, do not repeat the frame either — chain the definitions: ```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, adds a frame, and is then used as plain `OVER w3`. Names carry meaning here — `trailing_3d`, `to_date`, `whole_dept` beat `w1`, `w2`, `w3` in a query someone must maintain a year from now. ## What the refactor is and is not It is a textual factorization. The result set is byte-for-byte identical, and the language makes no promise about how many passes the engine takes over the data — identical inline specifications were already the same window, and consolidation is the engine's business, not something you buy by writing a name. Presenting this rewrite as a performance fix is a credibility loss in an interview; presenting it as maintainability plus a lower chance of a silently divergent window is the accurate claim. ## Costs to weigh The pattern has real downsides worth naming before adopting it as a standard. Refinement rules are unfamiliar to many reviewers, and a query whose windows chain two levels deep can be harder to read than the repetitive version it replaced — especially for someone debugging at three in the morning. Support is also uneven: named windows are standard and long available in PostgreSQL and in MySQL 8.0, while SQL Server only gained the clause in its 2022 release, so a query that must run on several engines or on an older installation may have to stay inline. And where a query is generated by a tool rather than hand-written, the repetition costs a maintainer nothing because nobody edits the output — the value of the refactor is proportional to how often a human touches the SQL. ## What an interviewer is listening for The frame-free base rule, the fallback when orderings diverge, and an honest statement that this is about maintainability rather than speed. A candidate who adds "and I'd check the target engine supports the clause before making it a convention" has covered the ground completely.