Window Functions Are the Point

GROUP BY makes you choose: keep the rows or get the aggregate. Collapse 320 fruit deliveries into six vintage averages and the deliveries are gone from the result. For years that trade was the price of aggregation, and people who needed both built temp tables, self-joins, or another spreadsheet.

Window functions remove the trade. A window function computes an aggregate, ranking, or offset alongside each row instead of collapsing rows into groups. The syntax is one clause: OVER (PARTITION BY ... ORDER BY ... frame), where PARTITION BY says what to compute within, ORDER BY says what sequence means, and the frame says how much of the partition each row can see. That's the syntax portion of this post. Everything else is what the clause is for, and the two places it bites.

One mechanical fact first, because T-SQL makes you feel it: window functions evaluate at the SELECT step, after WHERE and GROUP BY, per the processing order from post 2. Two consequences. You can't filter on a window function in the same query, and SQL Server has no QUALIFY clause to paper over that the way Snowflake does, so the idiom is always: compute the window in a CTE, filter in the next step. And every window sees only the rows that survived WHERE, which is sometimes the feature and sometimes the bug; hold that thought for LAG.

Examples on CellarDB, as ever (setup).

Dedupe: ROW_NUMBER and a survivorship policy

Post 6 found the double-entered row in BarrelFills: same barrel, lot, and fill date logged twice a day apart, 59.4 phantom gallons. The grain check found it. ROW_NUMBER removes it:

WITH Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY BarrelID, WineLotID, FillDate
               ORDER BY BarrelFillID
           ) AS rn
    FROM BarrelFills
)
SELECT BarrelFillID, BarrelID, WineLotID, FillDate, FillVolumeGal
FROM Ranked
WHERE rn = 1;

PARTITION BY is the grain you want; ORDER BY is the survivorship policy, and it deserves a decision rather than a default. ORDER BY BarrelFillID keeps the first row entered. If there were an audit timestamp, ordering by it descending would keep the latest correction instead. Pick the rule on purpose, because if your ORDER BY can tie, the survivor is arbitrary and can change between runs.

T-SQL will also let you delete through the CTE, which is how the duplicate actually dies rather than being filtered forever:

WITH Ranked AS ( ... )
DELETE FROM Ranked WHERE rn > 1;

Run the SELECT version first and read what you're about to delete. Always.

Top-1-per-group is the same pattern pointed at a different table. Post 6 pulled the latest amendment per contract with OUTER APPLY; with windows it reads:

ROW_NUMBER() OVER (PARTITION BY ContractID ORDER BY AmendmentDate DESC)

and WHERE rn = 1 in the next step. Current fill per barrel, latest lab reading per lot, most recent work order per cellar hand: once you see the shape, half your reporting backlog is this query.

Running totals, and the frame you didn't write

Running totals are where T-SQL's defaults bite. The harvest intake version looks like this:

SELECT Vintage, PickDate, Tons,
       SUM(Tons) OVER (
           PARTITION BY Vintage
           ORDER BY PickDate
       ) AS SeasonTons
FROM FruitLots;

It runs. It's wrong, in a way that only shows on tied dates, and during harvest two deliveries on the same day is routine. When you write ORDER BY in a windowed aggregate and no frame, SQL Server supplies a default frame of RANGE UNBOUNDED PRECEDING, and RANGE's idea of "current row" includes all peers, every row tied on the ORDER BY value. Both deliveries on September 14 get a total that already includes both:

A balance column where two events share the post-both-events balance is not a running total; reconcile intake against weigh tags with it and the tied days never tie. The fix is to state the frame you meant:

SUM(Tons) OVER (
    PARTITION BY Vintage
    ORDER BY PickDate, FruitLotID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS SeasonTons

Two changes, both load-bearing. ROWS counts physical rows, no peer behavior. And the ORDER BY gained FruitLotID as a tiebreaker, because a row-by-row total needs a total order; with ROWS and a tied sort, which 3.1 comes "first" is otherwise arbitrary. There's a performance bonus too: the default RANGE frame forces an on-disk spool inside the window operator while ROWS can stream in memory, so the explicit frame is faster as well as correct.

My default, stated as a default: every windowed aggregate with an ORDER BY gets an explicit ROWS frame and a deterministic sort. The day you want true RANGE semantics you'll know, and you'll write it down deliberately.

LAG: period-over-period

LAG(col) hands each row a value from an earlier row in its partition. Vintage-over-vintage yield per block:

WITH BlockVintage AS (
    SELECT BlockID, Vintage, SUM(Tons) AS Tons
    FROM FruitLots
    GROUP BY BlockID, Vintage
)
SELECT BlockID, Vintage, Tons,
       Tons - LAG(Tons) OVER (PARTITION BY BlockID ORDER BY Vintage) AS DeltaTons
FROM BlockVintage;

The CTE is not decoration. Run LAG on raw FruitLots and "previous row" means previous pick, so in a double-pick year the second pick compares against the first pick three days earlier instead of against last vintage. LAG inherits whatever grain you feed it; period-over-period requires one row per period first, the pre-aggregate discipline from post 5 again.

Two judgment notes. The first row of each partition gets NULL, which is honest: there is no prior vintage. LAG takes a default argument, LAG(Tons, 1, 0), and reaching for that zero re-creates the COALESCE lie from post 4: a block's first vintage becomes infinite growth over a vintage that never happened. Leave the NULL unless zero is the truth. Second, the WHERE-before-windows rule cuts both ways. Days since the last racking:

SELECT WineLotID, ScheduledDate,
       DATEDIFF(day,
                LAG(ScheduledDate) OVER (PARTITION BY WineLotID
                                         ORDER BY ScheduledDate, WorkOrderID),
                ScheduledDate) AS DaysSinceLast
FROM WorkOrders
WHERE WorkType = 'Racking';

The filter runs first, so LAG sees only rackings and "last" means last racking, which is what the cellar wanted. Drop a different question on the same skeleton, "days since any prior operation," and the same WHERE breaks it without a sound. The window sees the filtered set. Decide whether that's your feature or your bug per query.

Share of total

An OVER clause with no ORDER BY aggregates the whole partition onto every row, which makes percent-of-group a one-liner:

SELECT BlendID, WineLotID, VolumeUsedGal,
       CAST(100.0 * VolumeUsedGal
            / SUM(VolumeUsedGal) OVER (PARTITION BY BlendID)
            AS DECIMAL(5,1)) AS PctOfBlend
FROM BlendComponents;

CellarDB has a stored ComponentPct column, and this computed version beats it on principle: it always sums to 100, it can't drift when a component volume is edited, and it has no holes where someone forgot to fill it in (the stored column has two NULLs, which post 4 readers have already met). Derive shares; don't store them. An empty OVER () does the same trick against the grand total, so component share of blend and blend share of program can sit in adjacent columns of one query.

A cohort skeleton

Every retention analysis is the same three moves: stamp each entity with its first period, compare activity periods against the stamp, count. With windows that's a MIN and a GROUP BY:

WITH Cohorted AS (
    SELECT CustomerID,
           YEAR(ContractDate) AS ContractYear,
           MIN(YEAR(ContractDate)) OVER (PARTITION BY CustomerID) AS CohortYear
    FROM BulkContracts
)
SELECT CohortYear, ContractYear,
       COUNT(DISTINCT CustomerID) AS ActiveCustomers
FROM Cohorted
GROUP BY CohortYear, ContractYear
ORDER BY CohortYear, ContractYear;

Read it as: of the buyers whose first contract was 2022, how many came back in 2023, in 2024. Twenty-five customers makes this a toy, but the skeleton doesn't change at a million; swap in signup dates and order dates and it's the cohort chart every SaaS dashboard sells. Note COUNT(DISTINCT CustomerID) riding through a potential fan-out safely, the one distinct-aggregate post 5 endorsed.

The WINDOW clause

When several functions share one window, SQL Server 2022 lets you name it instead of repeating it:

SELECT WorkOrderID, WineLotID, ScheduledDate,
       LAG(ScheduledDate) OVER w AS PrevOp,
       ROW_NUMBER()       OVER w AS OpSeq
FROM WorkOrders
WINDOW w AS (PARTITION BY WineLotID ORDER BY ScheduledDate, WorkOrderID);

Less duplication, and one place to edit when the partition changes, which is how a six-window query stays maintainable. One trap: the clause needs database compatibility level 160, so if OVER w throws a syntax error on a 2022 server, check the compat level before doubting yourself.

Where this leaves the spreadsheet

Running totals, previous-row comparisons, shares of total, top-one-per-group: these are precisely the jobs I built helper columns and fragile sort orders for, for years, in Excel. The window versions are a few lines each, they don't break when someone re-sorts the data, and they run unchanged whether the table has 320 rows or 32 million.

None of it rescues a query that failed the earlier posts. A window over NULL-riddled data inherits the NULLs; a LAG at the wrong grain compares the wrong rows; a running total over a fanned-out join runs and totals the inflation beautifully. That ordering was the series' actual argument. Get the rows right first. Then the windows are the point.

— Lo