Window functions: ROW_NUMBER, RANK, LAG and running totals
Window functions calculate across a set of related rows without collapsing them like GROUP BY does. This lesson covers OVER with PARTITION BY and ORDER BY, ROW_NUMBER, RANK and DENSE_RANK, LAG and LEAD, and running totals, with the classic "latest row per group" pattern.
- StartFROM and WHEREPick the rows first
- Step 2PARTITION BYSplit rows into groups, such as one per customer
- Step 3ORDER BY in OVEROrder rows inside each group
- Step 4CalculateROW_NUMBER, RANK, LAG or SUM for each row
- EndEvery row keptThe result has one new column, no rows lost
Grouping without losing rows
GROUP BY turns many rows into one row per group. A window function keeps every row and adds a calculated column that looks at other rows in the same window, for example each order next to its customer's total or its rank.
The window is defined by OVER(...). PARTITION BY splits rows into groups, and ORDER BY sets the order inside each group.
Numbering and ranking
ROW_NUMBER gives 1, 2, 3 with no ties. RANK gives tied rows the same number and leaves gaps (1, 1, 3). DENSE_RANK gives ties the same number without gaps (1, 1, 2).
SELECT CustomerId, OrderId, Total,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderedAt DESC) AS RowNo,
RANK() OVER (ORDER BY Total DESC) AS SizeRank,
SUM(Total) OVER (PARTITION BY CustomerId) AS CustomerTotal
FROM Orders;Latest order per customer
The most common use: number the rows per group and keep row 1. A CTE is needed because window functions cannot be used directly in WHERE.
WITH Ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderedAt DESC) AS rn
FROM Orders o
)
SELECT CustomerId, OrderId, OrderedAt, Total
FROM Ranked
WHERE rn = 1;LAG, LEAD and running totals
LAG reads a value from an earlier row and LEAD from a later one, which makes month-over-month change easy. SUM with ORDER BY in the window gives a running total.
SELECT SalesMonth, Revenue,
Revenue - LAG(Revenue) OVER (ORDER BY SalesMonth) AS ChangeFromLastMonth,
SUM(Revenue) OVER (ORDER BY SalesMonth
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM MonthlySales;Tips
- Write ROWS BETWEEN ... for running totals; the default frame is RANGE, which is slower and treats tied ORDER BY values as one step.
- An index on (PartitionColumn, OrderColumn) lets SQL Server read rows already in window order and skip a sort.
- Other useful functions: NTILE(4) for quartiles, FIRST_VALUE and LAST_VALUE, and AVG(...) OVER for moving averages.
Interview question
How do you get the most recent order for each customer?
Number each customer's orders with ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderedAt DESC) inside a CTE or derived table, then select the rows where the number is 1. Window functions cannot appear in WHERE directly, which is why the extra step is needed. An index on (CustomerId, OrderedAt DESC) makes it fast.
Want the full details? Read the official Microsoft documentation for this topic.