SQL Window Functions

Window functions perform calculations across a set of rows that are related to the current row — without collapsing those rows into a single summary the way aggregate functions do with GROUP BY. Each row keeps its individual identity in the result, and the window function adds an additional calculated value alongside it.

Think of window functions like assigning rankings in a class — every student still appears individually in the list, but each student now also has a rank number beside their name based on their score.

Aggregate vs Window Functions

Window Function Syntax

function_name() OVER (
    [PARTITION BY column]
    [ORDER BY column]
    [ROWS or RANGE frame]
)

• PARTITION BY — Divides rows into groups (partitions). The function runs independently within each partition.
• ORDER BY — Defines the order of rows within each partition.
• OVER() — Required for all window functions. Defines the window (the set of rows the function operates on).

The Reference Table

1. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within the result or within each partition. Even if two rows have the same value, they get different row numbers.

SELECT StudentName, City, Score,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS OverallRank
FROM Students;

Result:

ROW_NUMBER With PARTITION BY

-- Rank students within each city
SELECT StudentName, City, Score,
       ROW_NUMBER() OVER (PARTITION BY City ORDER BY Score DESC) AS RankInCity
FROM Students;

Result:

The ranking restarts from 1 for each city (each partition).

2. RANK()

RANK() assigns ranks just like ROW_NUMBER(), but when two rows have the same value, they receive the same rank. The next rank is then skipped.

SELECT StudentName, Score,
       RANK() OVER (ORDER BY Score DESC) AS ScoreRank
FROM Students;

If two students both score 85, they both get rank 3 — and the next student gets rank 5 (rank 4 is skipped).

3. DENSE_RANK()

DENSE_RANK() also handles ties the same way as RANK() — same values get the same rank. The difference is that it does not skip ranks after a tie.

SELECT StudentName, Score,
       RANK()       OVER (ORDER BY Score DESC) AS RankWithGap,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students;

RANK vs DENSE_RANK for Tied Values

4. NTILE()

NTILE(n) divides the rows into n equal groups (buckets) and assigns each row a bucket number. Useful for creating quartiles, percentiles, or any equal-sized groupings.

-- Divide students into 3 equal groups by score
SELECT StudentName, Score,
       NTILE(3) OVER (ORDER BY Score DESC) AS ScoreGroup
FROM Students;

Result: Students are divided into 3 groups — top third, middle third, and bottom third based on score.

5. LAG() and LEAD()

LAG(column, n) accesses the value from n rows before the current row. LEAD(column, n) accesses the value from n rows after the current row. These are used to compare a row with its previous or next row.

SELECT StudentName, Score,
       LAG(Score, 1)  OVER (ORDER BY Score DESC) AS PreviousScore,
       LEAD(Score, 1) OVER (ORDER BY Score DESC) AS NextScore
FROM Students;

Result:

The first row has no previous row (PreviousScore = NULL), and the last row has no next row (NextScore = NULL).

6. SUM() as a Running Total

Aggregate functions like SUM() can be used as window functions to calculate a running (cumulative) total.

SELECT StudentName, Fees,
       SUM(Fees) OVER (ORDER BY StudentID) AS RunningTotal
FROM Students;

Result:

Window Function Summary

Key Points to Remember

• Window functions use OVER() — without it, the function behaves as a normal aggregate.
• PARTITION BY divides the window into groups — the function resets and runs independently within each group.
• ORDER BY inside OVER() controls the row ordering for the function calculation.
• RANK() skips numbers after ties; DENSE_RANK() does not skip.
• LAG() and LEAD() are powerful for comparing values across adjacent rows — common in financial and time-series analysis.

Summary

Window functions perform row-level calculations across related rows without eliminating any row from the output. They are ideal for rankings, running totals, row comparisons, and dividing data into groups — tasks that are awkward or impossible with simple GROUP BY aggregations. Mastering window functions is a major step toward writing professional-level analytical SQL.