SQL Server Window Functions Tutorial

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row like GROUP BY does. They use the OVER clause to define the window of rows to operate on. Window functions are essential for rankings, running totals, and comparing rows to their neighbors.

ROW_NUMBER

ROW_NUMBER() assigns a unique sequential number to each row within a partition. It is the most commonly used window function, often for pagination or deduplication.

-- Assign row numbers within each department
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS RowNum,
    FirstName, LastName, Department, Salary
FROM Employees;

-- Pagination: get rows 21-30
WITH Paged AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
SELECT * FROM Paged
WHERE RowNum BETWEEN 21 AND 30;

RANK and DENSE_RANK

RANK() assigns the same number to tied values but leaves gaps. DENSE_RANK() also handles ties but does not leave gaps. Choose based on whether gap-free numbering matters.

SELECT
    FirstName,
    Department,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

-- Top 3 earners per department
WITH Ranked AS (
    SELECT *,
        DENSE_RANK() OVER (
            PARTITION BY Department ORDER BY Salary DESC
        ) AS DeptRank
    FROM Employees
)
SELECT * FROM Ranked WHERE DeptRank <= 3;

LAG and LEAD

LAG() accesses a value from a previous row, and LEAD() accesses a value from a following row. These are invaluable for comparing consecutive records like month-over-month changes.

SELECT
    OrderMonth,
    Revenue,
    LAG(Revenue, 1) OVER (ORDER BY OrderMonth) AS PrevMonthRevenue,
    Revenue - LAG(Revenue, 1) OVER (ORDER BY OrderMonth) AS RevenueChange,
    LEAD(Revenue, 1) OVER (ORDER BY OrderMonth) AS NextMonthRevenue
FROM MonthlySales;

Running Totals with SUM

Aggregate functions like SUM, AVG, COUNT, MIN, and MAX work as window functions too. Use a frame clause (ROWS BETWEEN) to define the calculation range.

-- Running total of order amounts
SELECT
    OrderID,
    OrderDate,
    OrderTotal,
    SUM(OrderTotal) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Orders;

-- 3-month moving average
SELECT
    OrderMonth,
    Revenue,
    AVG(Revenue) OVER (
        ORDER BY OrderMonth
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3M
FROM MonthlySales;

FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE return the first or last value in a window frame. Be careful with LAST_VALUE -- you typically need to specify the frame to include all rows.

SELECT
    FirstName,
    Department,
    Salary,
    FIRST_VALUE(FirstName) OVER (
        PARTITION BY Department ORDER BY Salary DESC
    ) AS TopEarner,
    LAST_VALUE(FirstName) OVER (
        PARTITION BY Department ORDER BY Salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LowestEarner
FROM Employees;

Window functions unlock analytical capabilities that are difficult or impossible with GROUP BY alone. They let you rank, compare, and accumulate data while preserving every individual row in the result set. Practice combining PARTITION BY with ORDER BY and frame clauses to build sophisticated reports.