Code · May 31, 2026

SQL window functions in 2026: the complete practical guide

Q: Do window functions work with CTEs?

Yes, and that's the recommended pattern — define your base query in a CTE, then apply window functions in the outer SELECT, or vice versa.

Q: Are window functions slower than GROUP BY?

For the same operation, they're comparable. The difference is that window functions keep all rows, so the result set can be larger. Indexes on PARTITION BY + ORDER BY columns help significantly.

Q: Can I use multiple window functions in one query?

Yes. The planner typically shares the sort step if the windows have the same PARTITION BY and ORDER BY, so it's cheaper than it looks.

Q: What is FILTER (WHERE ...) in a window?

It's a SQL:2003 clause supported in Postgres and DuckDB that lets you conditionally include rows in an aggregate window: `SUM(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id)`.

Window functions are the most powerful SQL feature most developers underuse. Here is a concrete 2026 guide to rankings, running totals, and time-series gaps.

By ByteLedger Team

Window functions are the feature that separates developers who fight with SQL from developers who make it sing. The moment you stop reaching for a self-join or a correlated subquery to compare a row to its neighbours, and start writing OVER (PARTITION BY ... ORDER BY ...), your queries become shorter, faster, and readable. This guide covers everything you need in 2026.

What changed in 2026

DuckDB 1.x is now everywhere for analytical workloads, and its window function support is first-class — including EXCLUDE frame clauses that Postgres only got in version 14.
Postgres 16 added incremental sort improvements that make large window partitions significantly faster without explicit index hints.
MySQL 8.4 closed the last gaps in its window function coverage, so the excuse "we're on MySQL" no longer applies.
SQL:2023's pattern-matching extensions (MATCH_RECOGNIZE) are arriving in enterprise databases, but the core window spec is stable and universal.

The mental model

A window function runs after the WHERE and GROUP BY phases, but before ORDER BY and LIMIT. Every output row still exists; the function just gets to look sideways at related rows inside its "window."

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

The OVER clause defines three things: the partition (scope), the order (how rows are sequenced inside the window), and the frame (which rows count — defaults vary by function).

Ranking functions

Function	Ties	Gaps after tie
ROW_NUMBER()	Arbitrary	n/a
RANK()	Same rank	Yes (1,1,3)
DENSE_RANK()	Same rank	No (1,1,2)
NTILE(n)	Bucketed	n/a
PERCENT_RANK()	0.0–1.0 ratio	—

-- Rank employees by salary within each department
SELECT
  name,
  department,
  salary,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

Use DENSE_RANK when you want to avoid gap numbers. Use ROW_NUMBER when you need a unique sequential ID regardless of ties.

LAG and LEAD: comparing to neighbours

LAG(col, offset, default) looks back; LEAD(col, offset, default) looks forward.

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month)        AS prev_month,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS change
FROM monthly_revenue;

The third argument (default) prevents NULLs on the first/last row — always supply it.

Frame clauses

Without an explicit frame, functions that require ORDER BY default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For ROW_NUMBER and RANK, no frame applies. Knowing this prevents subtle bugs in running aggregates.

-- 7-day rolling average (row-based frame, not range-based)
SELECT
  event_date,
  value,
  AVG(value) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_metrics;

ROWS counts literal rows. RANGE groups rows with equal ORDER BY values. Use ROWS for time-series work where dates may repeat.

What changed in 2026

Running totals and deduplication

-- Deduplicate: keep only the latest row per user
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_events
)
SELECT * FROM ranked WHERE rn = 1;

This pattern replaces the classic "MAX in a subquery" approach and performs better on indexed tables.

Comparison table: window vs GROUP BY

Need	Use
Total per group, one row per group	GROUP BY + aggregate
Total per group, one row per original row	Window function
Compare each row to its group average	Window function
Top-N per group	ROW_NUMBER() window + WHERE rn <= N
Percent of total within group	SUM(x) OVER (PARTITION BY g)

How to pick the right function

Need a rank or position within a group? → RANK, DENSE_RANK, ROW_NUMBER.
Need to compare to the previous or next row? → LAG / LEAD.
Need a running aggregate (sum, avg)? → SUM/AVG with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Need a fixed aggregate per partition without collapsing? → SUM(x) OVER (PARTITION BY g) with no ORDER BY.
Need to bucket rows evenly? → NTILE(n).

Common mistakes

Confusing RANGE and ROWS. If your ORDER BY column has duplicates (e.g., two rows on the same date), RANGE will include both in each row's frame — doubling counts. Use ROWS for precise control.

Forgetting to supply a default to LAG/LEAD. The NULL on the boundary row often propagates into calculations silently.

Using window functions in WHERE. Window functions are computed after WHERE filtering, so you cannot filter on them directly. Wrap in a CTE or subquery first.

Over-partitioning. A partition with one row produces the same result as no partition for most functions — verify your PARTITION BY key actually groups meaningfully.

What to skip

Correlated subqueries as a substitute — SELECT *, (SELECT AVG(x) FROM t t2 WHERE t2.dept = t.dept) is almost always slower than a window aggregate.
Application-side rolling calculations — pulling raw rows into Python/JS to compute rolling averages you could compute in SQL wastes a round-trip.
OVER() with no PARTITION BY for large tables — a single global partition forces a full sort; verify you actually need the global window.

FAQ

Do window functions work with CTEs? Yes, and that's the recommended pattern — define your base query in a CTE, then apply window functions in the outer SELECT, or vice versa.

Are window functions slower than GROUP BY? For the same operation, they're comparable. The difference is that window functions keep all rows, so the result set can be larger. Indexes on PARTITION BY + ORDER BY columns help significantly.

Can I use multiple window functions in one query? Yes. The planner typically shares the sort step if the windows have the same PARTITION BY and ORDER BY, so it's cheaper than it looks.

What is FILTER (WHERE ...) in a window? It's a SQL:2003 clause supported in Postgres and DuckDB that lets you conditionally include rows in an aggregate window: SUM(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id).

Where to go next

See Database indexing explained in 2026 for making window queries fast at scale, and Caching strategies in 2026 for when to pre-aggregate results rather than compute them on every request.