Window Functions

14.1 Syntax

function() OVER ( [PARTITION BY ...] [ORDER BY ...] [ROWS|RANGE BETWEEN ... AND ...] )

14.2 Ranking Functions

SELECT ProductName, CategoryID, UnitPrice,
       ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS RowNum,
       RANK()       OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS Rnk,      -- gaps
       DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS DenseRnk, -- no gaps
       NTILE(4)     OVER (ORDER BY UnitPrice) AS Quartile
FROM dbo.Products;

-- Top-N per group
WITH R AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) rn FROM dbo.Products)
SELECT * FROM R WHERE rn <= 3;

14.3 Aggregate Window Functions

SELECT OrderID, OrderDate, CustomerID,
       COUNT(*)  OVER (PARTITION BY CustomerID) AS CustOrders,
       SUM(Amt)  OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal,
       AVG(Amt)  OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MovingAvg7,
       Amt * 100.0 / SUM(Amt) OVER () AS PctOfTotal
FROM dbo.OrderTotals;

14.4 Offset / Value Functions

SELECT OrderDate, Amt,
       LAG(Amt, 1, 0)  OVER (ORDER BY OrderDate) AS PrevAmt,
       LEAD(Amt)       OVER (ORDER BY OrderDate) AS NextAmt,
       Amt - LAG(Amt)  OVER (ORDER BY OrderDate) AS Change,
       FIRST_VALUE(Amt) OVER (ORDER BY OrderDate) AS FirstAmt,
       LAST_VALUE(Amt)  OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastAmt,
       LAG(Amt) IGNORE NULLS OVER (ORDER BY OrderDate) AS PrevNonNull   -- 2022+
FROM dbo.DailySales;

14.5 Analytic / Distribution Functions

SELECT ProductName, UnitPrice,
       PERCENT_RANK() OVER (ORDER BY UnitPrice) AS PctRank,
       CUME_DIST()    OVER (ORDER BY UnitPrice) AS CumeDist,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitPrice) OVER () AS MedianCont,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY UnitPrice) OVER () AS MedianDisc
FROM dbo.Products;

14.6 Window Frames: ROWS vs RANGE

ROWS is physical (a count of rows). RANGE is logical (rows with the same ORDER BY value are peers).

Default frame with ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. It is slower (on-disk spool) and can surprise you with ties. Specify ROWS explicitly.

Frame bounds: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.

14.7 The WINDOW Clause (2022+)

SELECT OrderDate, Amt,
       SUM(Amt) OVER w AS RunTotal,
       AVG(Amt) OVER w AS RunAvg
FROM dbo.DailySales
WINDOW w AS (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING);

14.8 Classic Problems Solved with Window Functions

Running totals, moving averages, year-over-year comparison Gaps and islands (consecutive sequences):

Deduplication, sessionization, Pareto (80/20), median, mode

WITH G AS (SELECT LoginDate, DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY LoginDate), LoginDate) AS grp
           FROM dbo.UserLogins WHERE UserID = 1)
SELECT MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS Days
FROM G GROUP BY grp ORDER BY StreakStart;