Window frames, QUALIFY filters, and CTE materialization rules can cut re-sorts and re-scans in analytic SQL for high-volume data teams. Read now.
Window Frames Decide How Much Gets Re-Sorted
Window functions are fast when the engine sorts a partition once and reuses that order for every calculation over it. They get slow when each function forces a different sort, or when a running frame recomputes an aggregate from scratch on every row. The frame clause—the ROWS or RANGE bound after your ORDER BY—controls this directly. An unbounded RANGE frame often makes the engine re-examine peer rows repeatedly, while an explicit ROWS BETWEEN frame lets it slide a fixed window and update incrementally.
A practical rule: align the PARTITION BY and ORDER BY of window functions you use together so the planner can share a single sort. If two functions in the same query disagree on partitioning or ordering, you pay for two sorts of the same data. When you only need the current row and a bounded number of neighbors, say so with ROWS instead of leaving the default RANGE frame in place.
QUALIFY Filters Without a Wrapping Subquery
You cannot put a window function in a WHERE clause, because filtering happens before windows are computed. The common workaround is to wrap the query in a subquery or CTE and filter the window result in an outer WHERE. QUALIFY removes that layer: it filters on the window function's output in the same SELECT, so ranking and deduplication read as one statement.
This matters for readability and for the planner. Fewer nesting layers mean fewer places where an engine might materialize an intermediate result or lose track of an ordering it already established. Typical uses include:
- Keeping the latest row per key with
ROW_NUMBER()andQUALIFY rn = 1 - Filtering to rows above a partitioned running total or rank threshold
- Deduplicating without a self-join or a separate grouped subquery
CTE Materialization: Reused vs. Recomputed
A common table expression is not automatically a temp table. Depending on the engine, a CTE may be inlined into the main query and re-evaluated everywhere it is referenced, or materialized once and reused. The difference is large when the CTE is expensive and referenced multiple times: inlining reruns the scan each time, while materialization pays for it once. Some databases let you request behavior with hints such as MATERIALIZED or NOT MATERIALIZED.
The tradeoff cuts both ways. Materializing a CTE that feeds a selective filter downstream can be wasteful, because inlining would let the optimizer push that filter into the scan and read far less data. Materialize when a costly result is reused several times and predicates cannot be pushed usefully. Inline when a filter or join on the outer query would prune the work if the optimizer could see through the CTE boundary.
Rewrites That Cut Re-Scans
Most analytic-SQL speedups come from removing redundant passes over the same data. Replacing a self-join that finds a per-group maximum with a single windowed pass avoids scanning the table twice. Collapsing a filtered-subquery pattern into QUALIFY removes a materialization boundary. Checking whether a heavily reused CTE is being recomputed can turn several scans back into one.
Read the query plan before and after each change, and watch specifically for repeated sort and scan operators. If two steps sort the same partition or scan the same source table, that is the work to eliminate—window frames, QUALIFY, and deliberate materialization choices are the levers that let you do it.