Window functions are the single highest-leverage piece of SQL syntax for BI work, and they're also the thing I see intermediate developers avoid the longest, usually because the OVER() clause looks intimidating until you've actually used it a few times. Once it clicks, running totals, rankings, and period-over-period comparisons stop being self-joins or correlated subqueries and become one clean pass over the data. This is the cheat sheet I wish I'd had earlier.

The core idea: OVER() defines a window, not a group

GROUP BY collapses rows. A window function keeps every row and computes a value across a related set of rows — the "window" — defined by PARTITION BY (which rows belong together) and ORDER BY (what order to process them in). That's the whole conceptual leap: you get row-level detail and an aggregate-like calculation in the same result set, with no self-join required.

SELECT
    CustomerKey,
    OrderDate,
    OrderAmount,
    SUM(OrderAmount) OVER (PARTITION BY CustomerKey ORDER BY OrderDate) AS RunningTotal
FROM FactOrders;

That one query gives every order row a running total of that customer's spend up to and including that order date, with no subquery and no join.

ROW_NUMBER, RANK, and DENSE_RANK

These three get confused constantly because they only differ in how they handle ties, and that difference matters a lot depending on what you're building.

  • ROW_NUMBER() — always unique, sequential, no gaps. Ties get an arbitrary but deterministic order based on any additional tiebreak you provide.
  • RANK() — ties get the same rank, and the next rank skips ahead by the number of tied rows (1, 2, 2, 4).
  • DENSE_RANK() — ties get the same rank, but the next rank doesn't skip (1, 2, 2, 3).
SELECT
    ProductName,
    CategoryName,
    SalesAmount,
    ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY SalesAmount DESC) AS RowNum,
    RANK()       OVER (PARTITION BY CategoryName ORDER BY SalesAmount DESC) AS Rnk,
    DENSE_RANK() OVER (PARTITION BY CategoryName ORDER BY SalesAmount DESC) AS DenseRnk
FROM vw_ProductSales;

The practical rule of thumb: use ROW_NUMBER() when you need exactly one row per group (deduplication, "top 1 per customer," pagination). Use RANK() when the business genuinely wants tied values to show the same competitive rank and doesn't mind a gap afterward (a leaderboard). Use DENSE_RANK() when ties should share a rank but you don't want gaps in the rank sequence (tiering customers into rank buckets where every bucket number should actually be used).

A very common real-world use: deduplication

WITH Ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY OrderLineNaturalKey
            ORDER BY ModifiedDateUtc DESC
        ) AS rn
    FROM staging.OrderLine_Raw
)
SELECT * FROM Ranked WHERE rn = 1;

This pulls the single most-recent version of each business-key row out of a staging table that might contain multiple versions of the same record from a change-tracking or CDC source — one of the most common real patterns in an incremental load.

LAG and LEAD

LAG() looks backward, LEAD() looks forward, both within a partition's order — no self-join needed to compare a row to the one before or after it.

SELECT
    CustomerKey,
    OrderDate,
    OrderAmount,
    LAG(OrderAmount) OVER (PARTITION BY CustomerKey ORDER BY OrderDate) AS PrevOrderAmount,
    OrderAmount - LAG(OrderAmount) OVER (PARTITION BY CustomerKey ORDER BY OrderDate) AS ChangeFromPrev
FROM FactOrders;

Both functions take an optional offset and default value: LAG(OrderAmount, 1, 0) looks one row back and returns 0 instead of NULL if there's no prior row — worth using explicitly rather than handling the null afterward in a CASE expression.

Running totals, done properly

The frame clause is what people miss, and it's the difference between a running total and an accidental full-partition sum. By default, when you use ORDER BY inside OVER() without an explicit frame, most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for aggregates, which behaves like a running total but ties (rows with the same order value) get summed together into the same running value. Being explicit avoids surprises across engines:

SELECT
    CustomerKey,
    OrderDate,
    OrderAmount,
    SUM(OrderAmount) OVER (
        PARTITION BY CustomerKey
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM FactOrders;

ROWS instead of the default RANGE frame guarantees a strict row-by-row running total regardless of tied order values, which is what "running total" almost always means in a business context.

A worked year-over-year example

Say you want each month's revenue alongside the same month last year, and the percentage change, from a monthly-aggregated view:

WITH MonthlyRevenue AS (
    SELECT
        DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS MonthStart,
        SUM(OrderAmount) AS Revenue
    FROM FactOrders
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT
    MonthStart,
    Revenue,
    LAG(Revenue, 12) OVER (ORDER BY MonthStart) AS RevenuePriorYear,
    CAST(Revenue AS DECIMAL(18,2))
        / NULLIF(LAG(Revenue, 12) OVER (ORDER BY MonthStart), 0) - 1 AS YoYGrowthPct
FROM MonthlyRevenue
ORDER BY MonthStart;

LAG(Revenue, 12) reaches back exactly twelve rows in a monthly series to get the same calendar month a year prior — this only works cleanly because the CTE first guarantees exactly one row per month with no gaps; if a month had zero orders and produced no row at all, the offset would silently reach back to the wrong month, so make sure the base series is gap-filled (typically by joining against a calendar/date spine table) before relying on a fixed numeric offset like this.

The equivalent thinking in DAX

Power BI developers doing the same running-total or YoY comparison reach for time intelligence functions rather than a window frame, but the underlying logic — compare the current row's context to a shifted context — is the same idea:

Running Total =
CALCULATE (
    SUM ( FactOrders[OrderAmount] ),
    FILTER (
        ALLSELECTED ( DimDate[Date] ),
        DimDate[Date] <= MAX ( DimDate[Date] )
    )
)

YoY Growth % =
VAR CurrentRevenue = SUM ( FactOrders[OrderAmount] )
VAR PriorYearRevenue =
    CALCULATE (
        SUM ( FactOrders[OrderAmount] ),
        SAMEPERIODLASTYEAR ( DimDate[Date] )
    )
RETURN
    DIVIDE ( CurrentRevenue - PriorYearRevenue, PriorYearRevenue )

SAMEPERIODLASTYEAR requires a proper marked date table, exactly the way the SQL version required a gap-free calendar spine — that's not a coincidence, it's the same underlying requirement showing up in both languages.

Window functions and DAX time intelligence solve the identical business problem from opposite directions: SQL's window frame explicitly walks a physical row order, DAX's CALCULATE/filter-context model implicitly walks a filtered set of dates. Knowing both means you can pick whichever layer — the query or the model — is the right place to put the logic, instead of defaulting to whichever one you learned first.

If there's one habit worth building, it's reaching for a window function before reaching for a self-join or a correlated subquery. Almost every "compare this row to a prior row," "rank within a group," or "running total within a partition" problem in BI reporting SQL has a one-pass window function answer, and it's almost always both more readable and faster than the join-based alternative.