Window Functions in SQL Server: ROW_NUMBER, RANK, DENSE_RANK, and Running Totals

The single most important thing to understand about window functions: unlike GROUP BY (Fundamentals Chapter 4), every original row stays in the result, enriched with a calculated value alongside it, rather than collapsed into one row per group. This is the tool for “rank each row within its group” or “running total as of this row” — questions GROUP BY structurally cannot answer, since it always reduces row count.

Ranking Within Groups

SELECT salesperson, region, amount, sale_date,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank_num
FROM dbo.Sale;

PARTITION BY is doing the conceptual work GROUP BY would do — splitting rows into groups — but instead of collapsing each group into one row, it just resets the ranking/calculation at each group boundary while keeping every row visible.

Running Totals

SELECT salesperson, region, sale_date, amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sale_date
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM dbo.Sale;

The ROWS BETWEEN ... AND CURRENT ROW clause is called a frame — it defines exactly which rows, relative to the current one, get included in the calculation. “Unbounded preceding to current row” means “every row from the start of this partition up through this one,” which is precisely what a running total means.

-- A different frame answers a different question: a 3-row moving average
SELECT salesperson, region, sale_date, amount,
    AVG(amount) OVER (PARTITION BY region ORDER BY sale_date
                       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM dbo.Sale;

How Ties Are Handled — The Difference That Actually Matters

Comparing to the Previous Row: LAG and LEAD

SELECT salesperson, sale_date, amount,
    LAG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS previous_sale,
    amount - LAG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS change_from_last
FROM dbo.Sale;

LAG reaches backward, LEAD reaches forward — both eliminate what used to require an awkward self-join to compare a row against its neighbor, exactly the SELF JOIN pattern from Fundamentals Chapter 5, now solved far more cleanly.

GROUP BY vs Window Functions