Window Functions: The Complete Guide to SQL Server's Most Powerful Feature

What Are Window Functions?

A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY, which collapses rows, window functions return a value for EVERY row.

-- GROUP BY (collapses rows):
SELECT Department, SUM(Salary)
FROM dbo.Employees
GROUP BY Department;
-- Result: 1 row per department

-- Window Function (keeps all rows):
SELECT
  EmployeeID,
  Department,
  Salary,
  SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotal
FROM dbo.Employees;
-- Result: ALL employees, plus department total for each

-- Every employee gets the department total calculated!

The OVER Clause: Core Syntax

FunctionName() OVER (
  [PARTITION BY column(s)]
  [ORDER BY column(s) ASC|DESC]
  [ROWS|RANGE frame_specification]
)

Example:
SUM(Salary) OVER (
  PARTITION BY Department
  ORDER BY Salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Breaking it down:
• PARTITION BY: Divide rows into groups (like GROUP BY)
• ORDER BY: Sort within each partition (for ranking, running totals)
• ROWS/RANGE: Define which rows to include (frame)

PARTITION BY: Divide Into Groups

-- No PARTITION BY (entire dataset is one window):
SELECT
  EmployeeID,
  Salary,
  SUM(Salary) OVER () AS GrandTotal
FROM dbo.Employees;
-- All employees' sum = one total for all 1000 rows

-- With PARTITION BY:
SELECT
  EmployeeID,
  Department,
  Salary,
  SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotal,
  COUNT(*) OVER (PARTITION BY Department) AS DepartmentCount
FROM dbo.Employees;
-- Each department gets its own total and count
// Employees in Sales see Sales total
// Employees in Engineering see Engineering total

Ranking Functions

Function	Purpose	Behavior with Ties	Example
ROW_NUMBER()	Unique row number	1, 2, 3, 4 (even if tied)	Distinct ordering
RANK()	Ranking with gaps	1, 2, 2, 4 (skips 3)	Competition ranking
DENSE_RANK()	Ranking no gaps	1, 2, 2, 3 (no skips)	Scoreboard ranking
NTILE(n)	Divide into n buckets	Quartiles, deciles	Top 25%, next 25%

ROW_NUMBER: Unique Sequential Number

-- Get top 3 employees per department by salary
SELECT
  Department,
  EmployeeID,
  Salary,
  ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
FROM dbo.Employees
WHERE ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) <= 3;
-- ERROR! Can't use window function in WHERE

-- Correct: Use CTE or subquery
SELECT *
FROM (
  SELECT
    Department,
    EmployeeID,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
  FROM dbo.Employees
) ranked
WHERE RankInDept <= 3;

-- Result: Top 3 per department

RANK vs. DENSE_RANK with Ties

-- Data:
Score: 100, 90, 90, 85

-- RANK():
ROW_NUMBER: 1, 2, 3, 4
RANK:       1, 2, 2, 4  (skips 3)

-- DENSE_RANK():
DENSE_RANK: 1, 2, 2, 3  (no skips)

-- RANK: Competition ranking (gold, silver, silver, bronze skip to bronze)
-- DENSE_RANK: Scoreboard ranking (1st, 2nd, 2nd, 3rd)

Aggregate Functions as Window Functions

-- SUM, AVG, COUNT, MIN, MAX can be window functions!

SELECT
  EmployeeID,
  Month,
  Sales,
  SUM(Sales) OVER (PARTITION BY EmployeeID ORDER BY Month) AS RunningTotal,
  AVG(Sales) OVER (PARTITION BY EmployeeID) AS AvgSales,
  COUNT(*) OVER (PARTITION BY EmployeeID) AS SalesCount,
  MAX(Sales) OVER (PARTITION BY EmployeeID) AS BestMonth
FROM dbo.MonthlySales;

-- Running total: Sales so far this year (per employee)
-- Average: Average all-time sales (per employee)
-- Max: Best sales month (per employee)

Analytical Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

LAG and LEAD: Access Previous/Next Rows

-- Year-over-year comparison
SELECT
  Year,
  Revenue,
  LAG(Revenue) OVER (ORDER BY Year) AS PreviousYear,
  Revenue - LAG(Revenue) OVER (ORDER BY Year) AS YearOverYearChange,
  LEAD(Revenue) OVER (ORDER BY Year) AS NextYear
FROM dbo.AnnualRevenue
ORDER BY Year;

-- LAG: Previous row value
-- LEAD: Next row value
-- Useful for: Comparing rows, detecting changes

FIRST_VALUE and LAST_VALUE: Get First/Last in Window

-- Get first and last order per customer
SELECT
  CustomerID,
  OrderDate,
  Amount,
  FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstOrder,
  LAST_VALUE(OrderDate) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- Important!
  ) AS LastOrder
FROM dbo.Orders;

-- FIRST_VALUE: First order date (per customer)
-- LAST_VALUE: Last order date (per customer)
-- NOTE: ROWS BETWEEN clause needed for LAST_VALUE (see Frames section)

Frames: Controlling the Window Boundaries

By default, when using ORDER BY, the frame is "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" (all rows from start to current). You can change this with explicit frame specifications.

-- Default frame (when ORDER BY is present):
SUM(Sales) OVER (ORDER BY Date)
-- = ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- Running total up to current row

-- Running total last 3 rows:
SUM(Sales) OVER (
  ORDER BY Date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- All rows in window:
SUM(Sales) OVER (
  ORDER BY Date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- Frame specifications:
UNBOUNDED PRECEDING: Start of partition
N PRECEDING: N rows back
CURRENT ROW: Current row
N FOLLOWING: N rows forward
UNBOUNDED FOLLOWING: End of partition

Running Totals: The Classic Example

-- Monthly sales with running totals
SELECT
  Month,
  Sales,
  SUM(Sales) OVER (ORDER BY Month) AS RunningTotal,
  SUM(Sales) OVER (ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Last3Months,
  AVG(Sales) OVER (ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Avg3Months
FROM dbo.MonthlySales;

-- Results:
Jan:  1000  |  1000  |  1000   |  1000
Feb:  1500  |  2500  |  2500   |  1250
Mar:  2000  |  4500  |  4500   |  1500
Apr:  1800  |  6300  |  5800   |  1933
May:  2200  |  8500  |  6000   |  2000

-- Running total cumulative (classic)
-- Last 3 months: Moving window
-- Avg 3 months: Moving average

Performance Tips

-- ✓ FAST: Filtered subquery
SELECT *
FROM (
  SELECT
    EmployeeID,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
  FROM dbo.Employees
  WHERE Active = 1  -- Filter early!
) ranked
WHERE RankInDept <= 3;

-- ✗ SLOW: Complex window function without filter
SELECT
  EmployeeID,
  Salary,
  ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RankAllEmployees  -- No partition
FROM dbo.Employees;
-- All 1M employees ranked globally
// Better: Add PARTITION BY if possible

-- ✓ TIP: Add indexes on partition/order columns
CREATE INDEX idx_Employees_DeptSalary
ON dbo.Employees(Department, Salary DESC);
-- Helps window function execution

Common Mistakes

Mistake 1: Using Window Function in WHERE Clause

-- ✗ ERROR:
SELECT EmployeeID, Salary
FROM dbo.Employees
WHERE ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) <= 3;
-- ERROR: Window functions not allowed in WHERE

-- ✓ CORRECT (use CTE or subquery):
WITH ranked AS (
  SELECT
    EmployeeID,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
  FROM dbo.Employees
)
SELECT EmployeeID, Salary
FROM ranked
WHERE RankInDept <= 3;

Mistake 2: Forgetting ROWS BETWEEN for LAST_VALUE

-- ✗ WRONG (gets current row, not last):
LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
-- Default: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
// Returns current row, not last! Wrong!

-- ✓ CORRECT (get actual last row):
LAST_VALUE(OrderDate) OVER (
  PARTITION BY CustomerID
  ORDER BY OrderDate
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
// Now returns truly last row

Mistake 3: Confusing ROW_NUMBER, RANK, DENSE_RANK

-- Same data, different results:
Score: 100, 90, 90, 85

ROW_NUMBER():   1, 2, 3, 4  (always unique)
RANK():         1, 2, 2, 4  (gaps when tied)
DENSE_RANK():   1, 2, 2, 3  (no gaps)

-- Use ROW_NUMBER: Get distinct rows (top N)
-- Use RANK: Competition scoring (gold, silver, silver, bronze)
-- Use DENSE_RANK: Scoreboard (1st, 2nd, 2nd, 3rd)

Best Practices

• ☐ Always use PARTITION BY to divide data into logical groups (faster than global windows).
• ☐ Add ORDER BY when ranking or calculating running totals.
• ☐ Use CTEs or subqueries to filter window function results (can't use in WHERE).
• ☐ For LAST_VALUE and LAST_VALUE: Explicitly set ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• ☐ Use OVER() [empty] only if you intentionally want global window (all rows).
• ☐ Index columns used in PARTITION BY and ORDER BY.
• ☐ Filter data before window function when possible (reduces rows processed).
• ☐ Use ROW_NUMBER for distinct rows; RANK/DENSE_RANK for scoring.
• ☐ LAG/LEAD useful for year-over-year comparisons and change detection.
• ☐ Moving averages: Use ROWS BETWEEN N PRECEDING AND N FOLLOWING.

The Bottom Line

Window functions are SQL Server's most powerful feature for analytics, ranking, and comparing rows. They eliminate the need for cursors, self-joins, and complex GROUP BY logic.

Master the OVER clause: PARTITION BY divides data, ORDER BY sorts, and ROWS BETWEEN defines boundaries. Use window functions for running totals, top N per group, ranking, and year-over-year comparisons.

Once you start using window functions, you'll realize how many problems become trivial: "Show me top 3 products per category"—one query. "Running total of sales"—one query. "Rank employees by department"—one query. Window functions are the secret to writing elegant, efficient SQL.