SQL Window Functions

Summary

1. Aggregate Window Functions
These functions perform calculations across a set of table rows that are somehow related to the current row.

SUM() OVER(…) – Running total or sum per partition
AVG() OVER(…) – Average per partition
COUNT() OVER(…) – Count rows in partition
MIN() OVER(…) – Minimum value in partition
MAX() OVER(…) – Maximum value in partition

2. Ranking Window Functions
These assign a rank or row number to each row within a partition.

ROW_NUMBER() OVER(…) – Unique sequential number per row in partition
RANK() OVER(…) – Rank with gaps for ties
DENSE_RANK() OVER(…) – Rank without gaps for ties
NTILE(n) OVER(…) – Divides partition into n buckets and assigns a bucket number

3. Value Navigation (Analytic) Functions
These functions return values from other rows in the window frame relative to the current row.

LAG(expression, offset, default) OVER(…) – Value from a previous row
LEAD(expression, offset, default) OVER(…) – Value from a following row
FIRST_VALUE(expression) OVER(…) – First value in the window frame
LAST_VALUE(expression) OVER(…) – Last value in the window frame
NTH_VALUE(expression, n) OVER(…) – The nth value in the window frame

Examples

1. Aggregate Window Functions

These perform calculations over a window of rows but return a value for each row.

Name	Sales	Date
Alice	100	2025-01-01
Alice	150	2025-01-03
Bob	200	2025-01-01
Alice	50	2025-01-05
Bob	300	2025-01-04

SUM() OVER()

SELECT
  Name,
  Sales,
  SUM(Sales) OVER (PARTITION BY Name ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM SalesData;

The window function SUM(Sales) OVER (...) calculates a running total of sales for each Name.

The window is partitioned by Name (so calculations are done separately for each person).

The rows are ordered by Date within each partition.

The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means the sum includes all rows from the first date up to the current row’s date.

Output:

Name	Sales	RunningTotal
Alice	100	100
Alice	150	250
Alice	50	300
Bob	200	200
Bob	300	500

AVG() OVER()

Customer_id	sales
101	200
102	150
101	300
103	400
102	250
101	100

SELECT 
customer_id, 
sales, 
AVG(sales) OVER (PARTITION BY customer_id) AS AvgSales 
FROM orders;

• The window function AVG(sales) OVER (PARTITION BY customer_id) calculates the average sales for each customer_id.
• The average is computed over all rows with the same customer_id.
• The result is shown on every row corresponding to that customer.

customer_id	sales	AvgSales
101	200	200
101	300	200
101	100	200
102	150	200
102	250	200
103	400	400

COUNT(*) OVER

department	employee_id
Sales	101
Sales	102
HR	201
HR	202
HR	203
IT	301

SELECT
  department,
  employee_id,
  COUNT(*) OVER (PARTITION BY department) AS DeptCount
FROM employees;

• The window function COUNT(*) OVER (PARTITION BY department) counts the total number of employees in each department.
• This count is repeated on every row for that department.
• No ordering is required here because the count is the same for all rows in the partition.

department	employee_id	DeptCount
Sales	101	2
Sales	102	2
HR	201	3
HR	202	3
HR	203	3
IT	301	1

2. Ranking Functions

Assign ranks or row numbers within partitions.

ROW_NUMBER() — Assigns unique row numbers

employee_id	department	salary
101	Sales	5000
102	Sales	7000
103	Sales	6000
201	HR	4500
202	HR	4800
301	IT	8000

SELECT
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS RowNum
FROM employees;

employee_id	salary	RowNum
102	7000	1
103	6000	2
101	5000	3
202	4800	1
201	4500	2
301	8000	1

The window function ROW_NUMBER() assigns a unique sequential number to each row within the partition defined by department.

Rows are ordered by salary in descending order within each department.

The highest salary in each department gets RowNum = 1, the next highest gets 2, and so on.

RANK() — Assigns rank, with gaps for ties

employee_id	department	salary
101	Sales	7000
102	Sales	7000
103	Sales	6000
201	HR	4800
202	HR	4500
301	IT	8000
302	IT	8000
303	IT	7500

SELECT
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS Rank
FROM employees;

The RANK() function assigns a rank to each employee within their department, ordering by salary descending.

Employees with the same salary get the same rank.

The next rank after a tie skips the appropriate number of positions (i.e., gaps in ranking).

employee_id	salary	Rank
101	7000	1
102	7000	1
103	6000	3
201	4800	1
202	4500	2
301	8000	1
302	8000	1
303	7500	3

DENSE_RANK() — Like RANK but no gaps

employee_id	department	salary
101	Sales	7000
102	Sales	7000
103	Sales	6000
201	HR	4800
202	HR	4500
301	IT	8000
302	IT	8000
303	IT	7500

SELECT
  employee_id,
  salary,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS DenseRank
FROM employees;

• DENSE_RANK() assigns ranks within each department, ordered by salary descending.
• Employees with the same salary get the same rank.
• Unlike RANK(), DENSE_RANK() does not skip ranks after ties. The next distinct value gets the next consecutive rank.

employee_id	salary	DenseRank
101	7000	1
102	7000	1
103	6000	2
201	4800	1
202	4500	2
301	8000	1
302	8000	1
303	7500	2

3. Value Navigation Functions

Access values from other rows relative to the current row.

LAG() — Value from previous row

employee_id	department	salary
101	Sales	5000
102	Sales	6000
103	Sales	7000
201	HR	4500
202	HR	4800
301	IT	8000

SELECT
  employee_id,
  salary,
  LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS PrevSalary
FROM employees;

LAG(salary, 1) returns the salary value from the previous row within the same department, based on the ascending order of salary.

For the first row in each department, there is no previous salary, so the result is NULL.

employee_id	salary	PrevSalary
101	5000	NULL
102	6000	5000
103	7000	6000
201	4500	NULL
202	4800	4500
301	8000	NULL

LEAD() — Value from next row

employee_id	department	salary
101	Sales	5000
102	Sales	6000
103	Sales	7000
201	HR	4500
202	HR	4800
301	IT	8000

SELECT
  employee_id,
  salary,
  LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS NextSalary
FROM employees;

LEAD(salary, 1) returns the salary value from the next row within the same department, based on the ascending order of salary.

For the last row in each department, there is no next salary, so the result is NULL

5000	6000
102	6000	7000
103	7000	NULL
201	4500	4800
202	4800	NULL
301	8000	NULL

LAST_VALUE() — Last value in the window frame

employee_id	department	salary
101	Sales	5000
102	Sales	7000
103	Sales	6000
201	HR	4500
202	HR	4800
301	IT	8000

SELECT
  employee_id,
  salary,
  LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MaxSalary
FROM employees;

• LAST_VALUE(salary) returns the last salary value in the ordered partition.
• The window frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures the entire partition is considered (not just up to the current row).
• When ordered by salary ascending, the last value is the highest salary in the department.

employee_id	salary	MaxSalary
101	5000	7000
103	6000	7000
102	7000	7000
201	4500	4800
202	4800	4800
301	8000	8000