60+ SQL Window Functions Interview Questions and Answers with Examples

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.

Feature	Window Functions	Aggregate Functions
Row Count	Retains all rows	Groups rows into one
Syntax	Uses `OVER()`	Uses `GROUP BY`
Example	`AVG(Salary) OVER()`	`AVG(Salary) GROUP BY Department`

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.

Function	Behavior	Example
RANK()	Skips numbers for duplicates	`1, 2, 2, 4`
DENSE_RANK()	No gaps in ranking	`1, 2, 2, 3`

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;

Function	Behavior
`SUM(Salary)`	Aggregates total salary
`SUM(Salary) OVER()`	Running total without collapsing rows

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;

Clause	Behavior
`ROWS`	Uses physical row count
`RANGE`	Uses logical grouping

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;

Clause	Meaning
`UNBOUNDED PRECEDING`	Includes all previous rows
`UNBOUNDED FOLLOWING`	Includes all future rows

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)

Clause	Behavior
`ROWS`	Uses physical row count
`RANGE`	Uses value-based grouping

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.

Function	Meaning	Formula
`PERCENT_RANK()`	Rank percentage (excluding self)	`(Rank - 1) / (TotalRows - 1)`
`CUME_DIST()`	Cumulative percentile (including self)	`Rank / TotalRows`

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.

Window Functions in SQL - Interview Questions and Answers

GIT

WordPress

SQL Joins

Functions in Python

Python Dictionaries

CSS3

Control Flow in Python

SQL Functions (Aggregate & Scalar)

Classes and Objects in Python

Probability and Statistics

Software Testing

Error Handling and Exceptions in Python

MongoDB

Lists in Python

ReactJs

Fundamental SQL Concepts

Random Blogs

Categories

Window Functions in SQL - Interview Questions and Answers

What are Window Functions in SQL?

How are Window Functions different from Aggregate Functions?

What is the ROW_NUMBER() function?

How does RANK() work in SQL?

What is the difference between RANK() and DENSE_RANK()?

What is NTILE() function in SQL?

How does SUM() work as a Window Function?

What is the difference between SUM() and SUM() OVER()?

What is the purpose of AVG() in Window Functions?

What is the FIRST_VALUE() function?

What is the LAST_VALUE() function?

What is LAG() and how is it used?

What is LEAD() and how is it used?

What does the PARTITION BY clause do in Window Functions?

What does the ORDER BY clause do in Window Functions?

What is the difference between ROWS and RANGE in Window Functions?

Can you combine multiple window functions in a query?

What is a Window Frame in SQL?

What is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING?

What is CURRENT ROW in a Window Function?

What is the difference between ROWS and RANGE in window frames?

How to calculate a running total of sales in an dataset?

How to rank students based on their marks in universities?

How to find the previous and next transaction amount for each customer?

How to get the first and last salary in each department?

How to rank salespersons within each region in India?

How to find the highest paid employee in each department?

How to get a moving average of the last 3 months' sales?

How to calculate the cumulative percentage of total revenue?

How to compare the sales growth percentage month over month?

Do Window Functions use indexes?

How to optimize window function performance?

Can window functions be used with DISTINCT?

How to debug slow Window Functions?

What databases support Window Functions?

Can you use Window Functions in WHERE or HAVING?

How to filter Window Function results?

Can you use Window Functions in UPDATE statements?

How to calculate a percentile using Window Functions?

What happens if there is no ORDER BY in a Window Function?

How to calculate a moving average of the last 6 months? revenue?

How does the CUME_DIST() function work?

What is the difference between PERCENT_RANK() and CUME_DIST()?

How does NTILE() help in distributing data equally?

How can you use Window Functions to detect duplicate rows?

How to get the top 3 selling products per category?

How to calculate the quarterly cumulative sales in dataset?

How can you assign alternative rankings (odd/even) to rows?

How to calculate a moving sum of last 7 days of sales?

How to track changes in employee salaries over time?

What happens if you don?t use ORDER BY in a Window Function?

Can Window Functions be used inside HAVING clause?

How to get top 5 employees per department based on performance?

How does WINDOW clause simplify queries?

How to calculate the first 10 transactions for each customer?

How does Window Functions handle NULL values?

Can you use DISTINCT with Window Functions?

How can you handle missing monthly sales data using Window Functions?

How to implement a percentile-based salary increment?

How to generate sequential batch numbers for order processing?

GIT

WordPress

SQL Joins

Functions in Python

Python Dictionaries

CSS3

Control Flow in Python

SQL Functions (Aggregate & Scalar)

Classes and Objects in Python

Probability and Statistics

Software Testing

Error Handling and Exceptions in Python

MongoDB

Lists in Python

ReactJs

Fundamental SQL Concepts

Random Blogs

Categories