SQL Window Functions: ROW_NUMBER, RANK, LAG, Running Totals

Q: What's the difference between GROUP BY and window functions?

GROUP BY collapses rows into one row per group. Window functions keep every row and add computed values based on related rows. Use GROUP BY for summaries, and window functions when you need row-level detail alongside group-level calculations.

SQL Window Functions

Window functions compute a value for each row using a set of related rows (its "window") without collapsing the rows the way GROUP BY does. They answer questions that are painful with joins and subqueries: each customer's order rank, the change since the previous month, a running total, a 7-day moving average, the top 3 products per category, or the latest row per key.

Supported by every major database (PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite, BigQuery, Snowflake, DuckDB), window functions are a staple of analytics, reporting, and data engineering SQL, and one of the highest-leverage SQL features to learn.

TL;DR

Syntax: function(...) OVER (PARTITION BY … ORDER BY … frame).
PARTITION BY splits rows into groups; ORDER BY orders rows within each partition.
Ranking: ROW_NUMBER (unique), RANK (gaps on ties), DENSE_RANK (no gaps), NTILE, PERCENT_RANK.
Offsets: LAG/LEAD (previous and next rows), FIRST_VALUE/LAST_VALUE, NTH_VALUE.
Aggregates as windows: SUM(...) OVER (ORDER BY …) gives running totals; frames like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW give moving averages.
Window functions run after WHERE/GROUP BY. Filter their results in an outer query, a CTE, or with QUALIFY where supported.

Quick Example

Every order row is kept, and each is annotated with values computed over that customer's order history.

Core Concepts

The OVER Clause

OVER (): the window is the entire result set. SUM(total) OVER () puts the grand total on every row, which is handy for percentages: total / SUM(total) OVER ().
PARTITION BY col: restart the calculation for each group, like GROUP BY, but rows stay separate.
ORDER BY col: defines order within the partition. It's required for ranking and offset functions, and it changes aggregate behavior to cumulative.
WINDOW w AS (...): name a window definition once and reuse it.

Ranking Functions

For scores 100, 90, 90, 80:

Make ORDER BY deterministic (add a unique tiebreaker like id) when using ROW_NUMBER for deduplication or pagination.

Offset Functions

LAG(col, n, default) and LEAD(col, n, default): values from n rows before or after, for period-over-period changes, session gaps, and detecting state transitions.
FIRST_VALUE(col), LAST_VALUE(col), NTH_VALUE(col, n): values at positions within the window frame. Mind the default frame with LAST_VALUE (see below).

Aggregate Window Functions and Frames

Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be a window function. With ORDER BY, a frame defines which rows around the current row are included:

ROWS counts physical rows.
RANGE uses value ranges on the ORDER BY column, treating peers (equal values) together.
GROUPS counts peer groups.

Default frame gotcha: with ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Running totals include all tied rows at once, and LAST_VALUE returns the current row rather than the partition's last value. Specify the frame explicitly.

Where Window Functions Fit in Query Order

Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT. So you can't reference a window result in WHERE. Wrap the query in a subquery or CTE, or use QUALIFY (Snowflake, BigQuery, DuckDB, Databricks):

Window functions can also wrap aggregates in a GROUP BY query: SUM(SUM(total)) OVER () gives a grand total alongside group subtotals.

Common Patterns

Best Practices

Always Specify Frames for Running Calculations

Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly. It avoids the RANGE-with-ties surprise, and ROWS frames are often faster.

Reuse Window Definitions

The WINDOW clause keeps queries readable and lets the database compute several functions over the same sort in one pass.

Index for Partition and Order

An index on (partition_col, order_col) can let the database read rows presorted instead of sorting the whole table, which is a big win for "latest per key" queries on large tables.

Prefer Window Functions Over Self-Joins

Correlated subqueries or self-joins to find "previous row" or "rank" scale poorly and are harder to read. Window functions express the intent directly, and planners execute them in a single sort-and-scan.

Common Mistakes

Filtering on a Window Function in WHERE

LAST_VALUE Returning the Current Row

With the default frame, LAST_VALUE(x) OVER (ORDER BY ts) sees only rows up to the current one. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or FIRST_VALUE with a descending order.

Non-Deterministic ROW_NUMBER

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at) with duplicate timestamps can pick different rows on each run. Add a tiebreaker: ORDER BY placed_at, id.

FAQ

What's the difference between GROUP BY and window functions?

GROUP BY collapses rows into one row per group. Window functions keep every row and add computed values based on related rows. Use GROUP BY for summaries, and window functions when you need row-level detail alongside group-level calculations.

ROW_NUMBER vs RANK vs DENSE_RANK?

All number rows in order within a partition. ROW_NUMBER always gives unique sequential numbers; RANK gives tied rows the same number and skips subsequent numbers; DENSE_RANK gives ties the same number without gaps. For "exactly one row per group", use ROW_NUMBER. For "top 3 including ties", use DENSE_RANK.

Are window functions slow?

They typically require sorting each partition, which costs about as much as an ORDER BY over the same rows. With appropriate indexes and filters applied first, they're usually much faster than equivalent self-joins or correlated subqueries.

Can I use window functions in UPDATE or DELETE?

Not directly, but you can compute them in a CTE or subquery and join back. For example, delete duplicates where ROW_NUMBER() > 1 by selecting their IDs in a CTE and deleting WHERE id IN (...).