Window Functions in SQL

Window functions perform calculations across a set of table rows related to the current row, without collapsing rows like GROUP BY.

Example:

SELECT EmployeeID, Name, Salary, 
       AVG(Salary) OVER() AS AvgSalary
FROM Employees;

Calculates the average salary without grouping rows.

ROW_NUMBER() assigns a unique row number to each record within a partition.

Example: Rank employees by salary

SELECT EmployeeID, Name, Salary, 
       ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum
FROM Employees;

RANK() assigns ranking numbers but skips numbers for duplicate values.

Example:

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

If two employees have the same salary, they get the same rank, but the next rank is skipped.

Example:

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

NTILE(n) distributes rows into n equal groups.

Example: Divide employees into 4 salary groups

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

It calculates a running total within a window.

Example: Running total of employee salaries

SELECT EmployeeID, Name, Salary, 
       SUM(Salary) OVER(ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

AVG() computes the average over a window.

Example:

SELECT EmployeeID, Name, Salary, 
       AVG(Salary) OVER(PARTITION BY Department) AS DeptAvgSalary
FROM Employees;

Calculates average salary per department.

Returns the first value in the window.

Example:

SELECT EmployeeID, Name, Salary, 
       FIRST_VALUE(Salary) OVER(ORDER BY Salary DESC) AS HighestSalary
FROM Employees;

Returns the last value in the window.

Example:

SELECT EmployeeID, Name, Salary, 
       LAST_VALUE(Salary) OVER(ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LowestSalary
FROM Employees;

LAG() fetches the previous row?s value.

Example: Compare employee salary with previous row

SELECT EmployeeID, Name, Salary, 
       LAG(Salary, 1) OVER(ORDER BY EmployeeID) AS PrevSalary
FROM Employees;

LEAD() fetches the next row?s value.

Example: Compare salary with next row

SELECT EmployeeID, Name, Salary, 
       LEAD(Salary, 1) OVER(ORDER BY EmployeeID) AS NextSalary
FROM Employees;

It groups data like GROUP BY, but retains all rows.

Example: Calculate department-wise salary rank

SELECT EmployeeID, Name, Department, Salary, 
       RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
FROM Employees;

It defines the order of calculations inside a window.

Example: Running total in order of hiring date

SELECT EmployeeID, Name, Salary, HireDate, 
       SUM(Salary) OVER(ORDER BY HireDate) AS RunningTotal
FROM Employees;

Example: Running total for last 2 rows

SUM(Salary) OVER(ORDER BY EmployeeID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Yes!

Example:

SELECT EmployeeID, Name, Salary, 
       RANK() OVER(ORDER BY Salary DESC) AS Rank,  
       SUM(Salary) OVER(ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

A window frame defines the subset of rows within a partition used for calculations.

Example: Running total using a frame

SELECT EmployeeID, Name, Salary, 
       SUM(Salary) OVER(ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees;

Example: Total salary for all employees

SUM(Salary) OVER(ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

It refers to the row being processed.

Example: Running average of last 3 rows

AVG(Salary) OVER(ORDER BY EmployeeID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Example:

SUM(Salary) OVER(ORDER BY EmployeeID RANGE BETWEEN INTERVAL 100 PRECEDING AND CURRENT ROW)

Includes rows within Salary range of 100, not just the last 3 rows.

Example: Sales of different products in an e-commerce company

SELECT OrderID, CustomerName, Product, OrderAmount, 
       SUM(OrderAmount) OVER(ORDER BY OrderDate) AS RunningTotal
FROM Orders;

SELECT StudentID, Name, Marks, 
       RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM Students;

Handles students with the same marks correctly.

SELECT CustomerID, TransactionID, Amount, 
       LAG(Amount, 1) OVER(PARTITION BY CustomerID ORDER BY TransactionDate) AS PreviousTransaction,
       LEAD(Amount, 1) OVER(PARTITION BY CustomerID ORDER BY TransactionDate) AS NextTransaction
FROM Transactions;

SELECT EmployeeID, Name, Department, Salary,  
       FIRST_VALUE(Salary) OVER(PARTITION BY Department ORDER BY Salary) AS FirstSalary,  
       LAST_VALUE(Salary) OVER(PARTITION BY Department ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSalary
FROM Employees;

SELECT SalespersonID, Name, Region, SalesAmount,  
       RANK() OVER(PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionRank
FROM Sales;

SELECT EmployeeID, Name, Department, Salary  
FROM (  
    SELECT EmployeeID, Name, Department, Salary,  
           RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank  
    FROM Employees  
) Ranked  
WHERE Rank = 1;

SELECT OrderID, CustomerID, OrderDate, Amount,  
       AVG(Amount) OVER(ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg  
FROM Orders;

SELECT OrderID, CustomerID, OrderAmount,  
       SUM(OrderAmount) OVER(ORDER BY OrderID) * 100 / SUM(OrderAmount) OVER() AS CumulativePercentage  
FROM Orders;

SELECT Month, TotalSales,  
       (TotalSales - LAG(TotalSales, 1) OVER(ORDER BY Month)) * 100 / LAG(TotalSales, 1) OVER(ORDER BY Month) AS GrowthPercentage  
FROM MonthlySales;

Window functions don?t directly use indexes but benefit from proper partitioning and sorting.

• Use PARTITION BY only when needed
• Reduce ORDER BY complexity
• Avoid using very large window frames

No, DISTINCT cannot be used inside OVER().

• Check execution plan (EXPLAIN ANALYZE)
• Use indexes on ORDER BY columns
• Optimize window frame definitions

Most modern databases:

• MySQL 8+
• PostgreSQL
• SQL Server
• Oracle 12c+

No, Window Functions cannot be used in WHERE or HAVING, only in SELECT and ORDER BY.

Use a subquery or Common Table Expression (CTE).

Example:

WITH RankedEmployees AS (  
    SELECT EmployeeID, Name, Salary,  
           RANK() OVER(ORDER BY Salary DESC) AS Rank  
    FROM Employees  
)  
SELECT * FROM RankedEmployees WHERE Rank <= 5;

Yes, but only in databases that allow it, like PostgreSQL.

Example:

UPDATE Employees  
SET Bonus = Salary * 0.10  
WHERE EmployeeID IN (  
    SELECT EmployeeID FROM (  
        SELECT EmployeeID, RANK() OVER(ORDER BY Salary DESC) AS Rank  
        FROM Employees  
    ) Ranked WHERE Rank <= 5  
);

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

Without ORDER BY, functions like RANK() assign random results instead of meaningful order.

SELECT Month, Revenue,  
       AVG(Revenue) OVER(ORDER BY Month ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS MovingAvgRevenue  
FROM MonthlyRevenue;

Helps in financial forecasting by calculating a rolling 6-month revenue average.

CUME_DIST() calculates the cumulative distribution percentile for a row.

Example: Find the percentile rank of employees by salary

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

Returns values between 0 and 1, showing the proportion of employees earning less than or equal to the given salary.

Example:

SELECT EmployeeID, Name, Salary,  
       PERCENT_RANK() OVER(ORDER BY Salary) AS PctRank,  
       CUME_DIST() OVER(ORDER BY Salary) AS CumeDist  
FROM Employees;

NTILE(n) divides rows into n equal-sized buckets.

Example: Divide employees into 4 salary brackets

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

SELECT *,  
       ROW_NUMBER() OVER(PARTITION BY Name, Age, Address ORDER BY ID) AS RowNum  
FROM Customers;

Returns duplicate rows with RowNum > 1.

SELECT * FROM (  
    SELECT ProductID, ProductName, Category, Sales,  
           RANK() OVER(PARTITION BY Category ORDER BY Sales DESC) AS Rank  
    FROM Products  
) Ranked WHERE Rank <= 3;

Extracts the top-selling products within each category.

SELECT Quarter, Region, Sales,  
       SUM(Sales) OVER(PARTITION BY Region ORDER BY Quarter) AS CumulativeSales  
FROM SalesData;

SELECT EmployeeID, Name,  
       CASE WHEN MOD(ROW_NUMBER() OVER(ORDER BY Salary), 2) = 0 THEN 'Even' ELSE 'Odd' END AS RankType  
FROM Employees;

SELECT OrderDate, SalesAmount,  
       SUM(SalesAmount) OVER(ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WeeklySales  
FROM Sales;

SELECT EmployeeID, Name, Salary, SalaryDate,  
       LAG(Salary, 1) OVER(PARTITION BY EmployeeID ORDER BY SalaryDate) AS PreviousSalary,  
       Salary - LAG(Salary, 1) OVER(PARTITION BY EmployeeID ORDER BY SalaryDate) AS SalaryChange  
FROM EmployeeSalaries;

Without ORDER BY, ranking and offset functions may return unpredictable results.

No, Window Functions cannot be used in HAVING or WHERE, only in SELECT or ORDER BY.

SELECT * FROM (  
    SELECT EmployeeID, Name, Department, PerformanceScore,  
           RANK() OVER(PARTITION BY Department ORDER BY PerformanceScore DESC) AS Rank  
    FROM Employees  
) Ranked WHERE Rank <= 5;

Instead of repeating OVER() in multiple functions, use WINDOW:

SELECT EmployeeID, Name, Salary,  
       SUM(Salary) OVER emp_window AS RunningTotal,  
       AVG(Salary) OVER emp_window AS AvgSalary  
FROM Employees  
WINDOW emp_window AS (PARTITION BY Department ORDER BY Salary);

SELECT * FROM (  
    SELECT CustomerID, TransactionID, Amount,  
           ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TransactionDate) AS RowNum  
    FROM Transactions  
) Ranked WHERE RowNum <= 10;

• Ranking functions treat NULLs as lowest values.
• Offset functions return NULL if no previous/next value exists.

Example:

SELECT EmployeeID, Name, Salary,  
       LAG(Salary, 1, 0) OVER(ORDER BY Salary) AS PrevSalary  
FROM Employees;

Replaces NULL with 0.

No, DISTINCT is not allowed inside OVER().

Use COALESCE() with LAG():

SELECT Month,  
       COALESCE(Sales, LAG(Sales) OVER(ORDER BY Month)) AS FilledSales  
FROM SalesData;

Fills missing sales data with the previous month's value.

SELECT EmployeeID, Name, Salary,  
       CASE  
           WHEN PERCENT_RANK() OVER(ORDER BY Salary) <= 0.25 THEN Salary * 1.10  
           ELSE Salary * 1.05  
       END AS NewSalary  
FROM Employees;

Employees in the lowest 25% salary get a 10% raise, others get 5%.

SELECT OrderID, OrderDate,  
       NTILE(5) OVER(ORDER BY OrderDate) AS BatchNumber  
FROM Orders;

Divides orders into 5 processing batches.