Window Functions

Window functions compute values over a set of rows (the "window") without collapsing the result like GROUP BY does. T-SQL has a rich set: ranking, offset, aggregate, and distribution functions, all accessed via the OVER clause.

Anatomy of OVER

function(args) OVER (
    [PARTITION BY  cols]
    [ORDER BY      cols]
    [ROWS | RANGE  frame_spec]
)

PARTITION BY — divides rows into independent groups (like GROUP BY for the window).
ORDER BY — defines the row order inside each partition.
ROWS / RANGE — specifies which rows around the current row form the window frame.

Ranking Functions

Function	Behaviour
`ROW_NUMBER()`	Strict sequential 1, 2, 3, ... — ties broken arbitrarily
`RANK()`	Ties get the same rank, leaves gaps (1, 2, 2, 4)
`DENSE_RANK()`	Ties get the same rank, no gaps (1, 2, 2, 3)
`NTILE(n)`	Buckets rows into n equal-sized tiles

SELECT  employee_id,
        first_name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
        RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drk,
        NTILE(4)     OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM    employees;

Top-N-Per-Group

The classic ROW_NUMBER use case:

;WITH ranked AS (
    SELECT  e.*,
            ROW_NUMBER() OVER (PARTITION BY department_id
                               ORDER BY salary DESC) AS rn
    FROM    employees e
)
SELECT  employee_id, first_name, department_id, salary
FROM    ranked
WHERE   rn <= 3;

Offset Functions: LAG and LEAD

LAG(col, n, default) looks back n rows; LEAD looks forward.

-- Compare each order to the customer's previous order
SELECT  customer_id,
        order_id,
        order_date,
        total,
        LAG(total)      OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_total,
        total -
        LAG(total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS delta
FROM    orders;

FIRST_VALUE / LAST_VALUE

-- Each employee's salary alongside their department's highest salary
SELECT  employee_id,
        first_name,
        salary,
        department_id,
        FIRST_VALUE(salary) OVER (PARTITION BY department_id
                                  ORDER BY salary DESC) AS dept_max_salary,
        LAST_VALUE(salary)  OVER (PARTITION BY department_id
                                  ORDER BY salary DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING) AS dept_min_salary
FROM    employees;

⚠

LAST_VALUE with an ORDER BY defaults to the frame UNBOUNDED PRECEDING TO CURRENT ROW, so it returns the current row's value, not the partition's last. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you want the partition's actual last value.

Aggregate Window Functions

SUM, AVG, COUNT, MIN, MAX can all be windowed.

Running Total

SELECT  customer_id,
        order_date,
        total,
        SUM(total) OVER (PARTITION BY customer_id
                         ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND CURRENT ROW) AS running_total
FROM    orders
ORDER BY customer_id, order_date;

Moving Average

-- 3-day moving average of daily order totals
SELECT  order_date,
        SUM(total) AS day_total,
        AVG(SUM(total)) OVER (ORDER BY order_date
                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM    orders
GROUP BY order_date
ORDER BY order_date;

Frame Specs

Spec	Meaning
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Running total
`ROWS BETWEEN n PRECEDING AND CURRENT ROW`	Trailing window of n+1 rows
`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	Tail aggregate
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	Whole partition
`RANGE BETWEEN ...`	Frame by value, not row position (for date ranges, etc.)

-- 7-day rolling sum using RANGE (works correctly with date gaps)
SELECT  order_date,
        SUM(total) OVER (ORDER BY order_date
                         RANGE BETWEEN INTERVAL '6' DAY PRECEDING
                                   AND CURRENT ROW) AS rolling_7d
FROM    orders;

Dedupe With ROW_NUMBER

-- Keep the most recent record per (customer_id, product_id)
;WITH dups AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, product_id
                                 ORDER BY updated_at DESC) AS rn
    FROM   customer_products
)
DELETE FROM dups WHERE rn > 1;

Best Practices

Window functions usually outperform self-joins or correlated subqueries for the same problem.
Always think about both PARTITION BY and ORDER BY — getting either wrong silently changes the answer.
Specify the frame explicitly with ROWS BETWEEN ... — defaults can surprise you (especially for LAST_VALUE).
For dedupe, deletion via a CTE with ROW_NUMBER is idiomatic.

Summary

OVER defines a window with PARTITION BY, ORDER BY, and a frame spec.
ROW_NUMBER, RANK, DENSE_RANK, NTILE rank rows; LAG/LEAD peek across rows.
Aggregate window functions (SUM, AVG, ...) compute running totals and moving averages.
Watch the default frame on LAST_VALUE; always specify ROWS BETWEEN ... AND ... when you need the whole partition.