SQL Optimization [2026] Window Functions & CTE Rewrites

Quick Reference

• Use windows when you need row context without collapsing rows.
• Use grouped aggregates when you can reduce cardinality early.
• Use temp tables or materialized stages when the same heavy subquery feeds multiple branches.
• Before sharing sample data or plans with a team, scrub sensitive fields with the Data Masking Tool.

Commands by Purpose

Filter top-N per partition

SELECT
  customer_id,
  order_id,
  event_ts
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY event_ts DESC
) = 1;

Replace row-to-row self-joins

SELECT
  session_id,
  event_ts,
  event_value - LAG(event_value) OVER (
    PARTITION BY session_id
    ORDER BY event_ts
  ) AS delta
FROM events;

Fix misleading default frames

SELECT
  account_id,
  event_ts,
  LAST_VALUE(status) OVER (
    PARTITION BY account_id
    ORDER BY event_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS final_status
FROM account_events;

Pre-aggregate before the join

WITH order_totals AS (
  SELECT
    customer_id,
    region_id,
    SUM(amount) AS gross
  FROM fact_orders
  WHERE order_date >= @start_date
  GROUP BY customer_id, region_id
)
SELECT
  ot.customer_id,
  dr.region_name,
  ot.gross
FROM order_totals ot
JOIN dim_region dr
  ON dr.region_id = ot.region_id;

Inline or materialize a CTE deliberately

WITH filtered AS NOT MATERIALIZED (
  SELECT
    user_id,
    event_date,
    revenue
  FROM fact_events
  WHERE event_date >= @start_date
)
SELECT
  user_id,
  SUM(revenue) AS total_revenue
FROM filtered
GROUP BY user_id;

CREATE TEMP TABLE heavy_stage AS
SELECT
  customer_id,
  region_id,
  SUM(amount) AS gross
FROM fact_orders
WHERE order_date >= @start_date
GROUP BY customer_id, region_id;

SELECT hs.customer_id, dr.region_name, hs.gross
FROM heavy_stage hs
JOIN dim_region dr
  ON dr.region_id = hs.region_id;

Configuration

Planner-friendly defaults

• Align physical partitioning or clustering with the columns used in your largest filters.
• Keep window partitions narrow where possible; wide partitions multiply sort pressure.
• Prefer stable sort keys with good cardinality for ranking and dedupe patterns.
• Persist expensive transformations if the same shaped data feeds many dashboards or jobs.
• Keep SQL readable; if you share snippets publicly, clean them with the Code Formatter after the rewrite is stable.

What to inspect in the plan

Advanced Usage

Use QUALIFY to keep ranking local

• When your engine supports it, QUALIFY filters window results after the window step without forcing an extra wrapper query.
• It is especially useful for latest-row, top-N, and dedupe patterns.

Trade exact ranking for approximate bucketing when scale demands it

• BigQuery documents that heavy NTILE usage can require a global ORDER BY and may hit resource limits.
• For percentile-style grouping at massive scale, approximate quantiles can be the cheaper path if exact bucket boundaries are not mandatory.

SELECT
  APPROX_QUANTILES(latency_ms, 100) AS latency_percentiles
FROM api_requests;

Split logic when the optimizer cannot

• If one monster query both transforms and serves analytics, separate the expensive stage from the final read path.
• Temp tables, scheduled summary tables, and materialized views are often easier to operate than one heroic statement.

Plan Checks

Fast verification loop

1. Capture the baseline plan and runtime metrics.
2. Rewrite one pattern at a time: ranking, frame, join order, or CTE reuse.
3. Re-run with the same date range and concurrency conditions.
4. Compare scan bytes, sort stages, spill, and output row counts.
5. Keep the simpler query only if the plan is measurably better.

EXPLAIN ANALYZE
SELECT
  customer_id,
  SUM(amount) AS gross
FROM fact_orders
WHERE order_date >= @start_date
GROUP BY customer_id;

• For shared benchmarks, keep the same predicates, same warehouse or slot conditions, and same output shape.
• If the rewrite changes semantics, stop calling it an optimization and document it as a behavior change.