SQL Window Functions - Complete Guide
Window Functions allow you to perform calculations across a set of table rows that are somehow
related to the current row. They are useful for ranking, running totals, moving averages, and
analytics without collapsing rows like GROUP BY.
Category Functions
Ranking Functions ROW_NUMBER, RANK, DENSE_RANK, NTILE
Aggregate Functions (with OVER) SUM, AVG, COUNT, MIN, MAX
Value Functions LEAD, LAG, FIRST_VALUE, LAST_VALUE
Analytic Functions CUME_DIST, PERCENT_RANK
ROW_NUMBER()
Assigns a unique number to each row.
Example:
SELECT Name, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum FROM
Employees;
Name Salary RowNum
Rohit 70000 1
Neha 60000 2
Amit 50000 3
RANK()
Gives ranking; same values get the same rank but next rank is skipped.
Example:
SELECT Name, Salary, RANK() OVER(ORDER BY Salary DESC) AS Rank FROM Employees;
Name Salary Rank
Rohit 70000 1
Neha 60000 2
Amit 50000 3
DENSE_RANK()
Similar to RANK but does not skip ranks.
Example:
SELECT Name, Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRank FROM
Employees;
Name Salary DenseRank
Rohit 70000 1
Neha 60000 2
Amit 50000 3
NTILE(n)
Divides rows into n buckets.
Example:
SELECT Name, Salary, NTILE(2) OVER(ORDER BY Salary DESC) AS Bucket FROM Employees;
Name Salary Bucket
Rohit 70000 1
Neha 60000 1
Amit 50000 2
SUM() OVER()
Calculates running totals or partitioned sums.
Example:
SELECT Month, Sales, SUM(Sales) OVER(ORDER BY Month) AS RunningTotal FROM Sales;
Month Sales RunningTotal
Jan 1000 1000
Feb 1500 2500
Mar 1200 3700
LEAD()
Returns value from the next row.
Example:
SELECT Name, Salary, LEAD(Salary) OVER(ORDER BY Salary DESC) AS NextSalary FROM
Employees;
Name Salary NextSalary
Rohit 70000 60000
Neha 60000 50000
Amit 50000 45000
LAG()
Returns value from the previous row.
Example:
SELECT Name, Salary, LAG(Salary) OVER(ORDER BY Salary DESC) AS PrevSalary FROM
Employees;
Name Salary PrevSalary
Rohit 70000 NULL
Neha 60000 70000
Amit 50000 60000
FIRST_VALUE()
Returns the first value in the ordered set.
Example:
SELECT Name, Salary, FIRST_VALUE(Salary) OVER(ORDER BY Salary DESC) AS Highest
FROM Employees;
Name Salary Highest
Rohit 70000 70000
Neha 60000 70000
LAST_VALUE()
Returns the last value in the ordered set.
Example:
SELECT Name, Salary, LAST_VALUE(Salary) OVER(ORDER BY Salary DESC ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Lowest FROM
Employees;
Name Salary Lowest
Rohit 70000 40000
Neha 60000 40000
CUME_DIST()
Cumulative distribution (0-1).
Example:
SELECT Name, Salary, CUME_DIST() OVER(ORDER BY Salary) AS CumeDist FROM
Employees;
Name Salary CumeDist
Raj 40000 0.2
Simran 45000 0.4
PERCENT_RANK()
Percentile rank (0-1).
Example:
SELECT Name, Salary, PERCENT_RANK() OVER(ORDER BY Salary) AS PercentRank FROM
Employees;
Name Salary PercentRank
Raj 40000 0.00
Simran 45000 0.25