Mastering SQL Server Window Functions – Concepts

SQL Server window functions are essential for analytics, reporting, and business intelligence. They allow you to perform calculations across sets of rows while retaining row-level detail. In this article, we’ll explore the most important window functions with real-world examples.

Core SQL Server Window Functions

ROW_NUMBER()

Assigns a unique sequential number to rows within a partition, ordered by a specified column.

Use cases:
Deduplication (keep first record per group)
Top-N queries (e.g., top 3 earners per department)
Generating unique row IDs

Example:

SELECT
EmployeeID, Salary,
       ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Emeployes;

RANK() & DENSE_RANK()

Both assign rankings, but handle ties differently:
RANK(): Leaves gaps when ties occur.
DENSE_RANK(): No gaps with ties.

Use cases:
- Leaderboards (sales competitions, KPIs)
- Performance rankings

Example:

SELECT EmployeeID, Salary,
       RANK() OVER(ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRank
FROM Employees;

NTILE(n)

Divides rows into n buckets and assigns each row to a bucket.

Use cases:
Percentile ranking
Customer segmentation
Performance distribution

Example:

SELECT EmployeeID, Salary,
       NTILE(4) OVER(ORDER BY Salary DESC) AS Quartile
FROM Employees;

LAG() & LEAD()

Allow you to access values from previous or next rows.
LAG(): Looks backward
LEAD(): Looks forward

Use cases:
Month-over-month sales growth
Detecting customer churn (gap between orders)
Salary progression tracking
Example:

SELECT EmployeeID, Month, Salary,
       LAG(Salary) OVER(PARTITION BY EmployeeID ORDER BY Month) AS PrevSalary,
       Salary - LAG(Salary) OVER(PARTITION BY EmployeeID ORDER BY Month) AS SalaryChange
FROM SalaryHistory;

SUM() & AVG() with OVER()

Apply aggregate functions while retaining detail rows.

Use cases:
Running totals
Moving averages
Department totals while keeping employee detail
Example:

SELECT EmployeeID, Department, Salary,
       SUM(Salary) OVER(PARTITION BY Department) AS DeptTotal
FROM Employees;

GROUP BY vs OVER()

GROUP BY collapses rows into one per group. OVER() keeps all rows while adding aggregated insights.

Example:

-- GROUP BY: One row per department
SELECT Department, SUM(Salary) AS DeptTotal
FROM Employees
GROUP BY Department;

-- OVER(): Keeps each employee
SELECT EmployeeName, Department, Salary,
       SUM(Salary) OVER(PARTITION BY Department) AS DeptTotal
FROM Employees;

Percentile functions.

PERCENTILE_CONT(): interpolates between values (continuous).

PERCENTILE_DISC(): picks actual row value (discrete).

Use cases: median salary, top decile cutoff.

Example:

SELECT DISTINCT
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER () AS MedianSalary
FROM Employees;

CUME_DIST()

Cumulative distribution: proportion of rows with values <= current row.

Use cases: identify top 10% customers, cumulative sales distribution.

Example:

SELECT EmployeeID, Salary,
       CUME_DIST() OVER(ORDER BY Salary DESC) AS CumeDistribution
FROM Employees;

PERCENT_RANK()

Relative rank of a row between 0 and 1.

Use cases: percentile ranking of salaries, student scores, sales performance.

Example:

SELECT EmployeeID, Salary,
       PERCENT_RANK() OVER(ORDER BY Salary) AS PercentRank
FROM Employees;

LAST_VALUE()

Returns the last value in the window based on ORDER BY.

By default, only looks up to the current row. To get the true last value, extend the frame.

Example:

SELECT EmployeeID, Month, Salary,
       LAST_VALUE(Salary) OVER(
            PARTITION BY EmployeeID ORDER BY Month
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS LastSalary
FROM SalaryHistory;

By default, LAST_VALUE() returns the value of the last row in the current frame, 
which by default ends at the current row (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). 
So without explicitly expanding the frame, LAST_VALUE() might return the current row’s value instead of the actual last value.

FIRST_VALUE()

Returns the first value in the window based on ORDER BY.

Use cases: first sale of the year, initial salary, earliest event.

Example:

SELECT EmployeeID, Month, Salary,
       FIRST_VALUE(Salary) OVER(PARTITION BY EmployeeID ORDER BY Month) AS FirstSalary
FROM SalaryHistory;

Real-World Business Examples

Top 3 Salespeople per Region

SELECT * FROM (
   SELECT Region, SalesPerson, TotalSales,
          ROW_NUMBER() OVER(PARTITION BY Region ORDER BY TotalSales DESC) AS rn
   FROM Sales
) t
WHERE rn <= 3;

Month-over-Month Sales Growth

SELECT Month, TotalSales,
       LAG(TotalSales) OVER(ORDER BY Month) AS PrevMonth,
       TotalSales - LAG(TotalSales) OVER(ORDER BY Month) AS Growth
FROM MonthlySales;

Detecting Customer Churn

SELECT CustomerID, OrderDate,
       LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrder,
       DATEDIFF(day, LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) AS DaysBetween
FROM Orders;

Rolling averages, running totals, sliding windows.

Example: 3-month moving average
SELECT Month, Sales,
       AVG(Sales) OVER(ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM MonthlySales;