Window Functions in SQL

SQL window functions allow performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value. They are commonly used for tasks like aggregates, rankings and running totals.

The OVER clause defines the “window” of rows for the calculation. It can:

• PARTITION BY: divide the data into groups.
• ORDER BY: specify the order of rows within each group.

With this, functions such as SUM(), AVG(), ROW_NUMBER(), RANK() and DENSE_RANK() can be applied in a controlled way.

Syntax 

SELECT column_name1, 
       window_function(column_name2) 
       OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column
FROM table_name;

Key Terms

• window_function: Any aggregate or ranking function (SUM(), AVG(), ROW_NUMBER(), etc.)
• column_name1: Regular column(s) to be selected in the outputand column_name2: Column on which the window function is applied
• column_name3: Column used for dividing rows into groups (PARTITION BY)and column_name4: Column used to define order of rows within each partition (ORDER BY)
• new_column: Alias for calculated result of the window function
• table_name: table from which data is selected

Types of Window Functions in SQL

SQL window functions can be categorized into two primary types: aggregate window functions and ranking window functions. These two serve different purposes but share a common ability to perform calculations over a defined set of rows while retaining the original data.

To understand these types better, we will use an Employees table that stores details like employee name, age, department and salary.

Employees Table

1. Aggregate Window Function

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:

• SUM(): Sums values within a window.
• AVG(): Calculates the average value within a window.
• COUNT(): Counts the rows within a window.
• MAX(): Returns the maximum value in the window.
• MIN(): Returns the minimum value in the window.

Example: Using AVG() to Calculate Average Salary within each department

SELECT Name, Age, Department, Salary, 
       AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
 FROM employee

Output

Explanation:

• For Finance: (50,000 + 50,000 + 20,000) ÷ 3 = 40,000.
• For Sales: (30,000 + 20,000) ÷ 2 = 25,000.
• This average is repeated for each employee in the same department.

2. Ranking Window Functions

These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:

• RANK(): Assigns ranks to rows, skipping ranks for duplicates.
• DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
• ROW_NUMBER(): Assigns a unique number to each row in the result set.
• PERCENT_RANK(): Shows the relative rank of a row as a percentage between 0 and 1.

2.1 RANK Function

It assigns ranks to rows within a partition, with the same rank given to rows with identical values. If two rows share the same rank, the next rank is skipped. 

Example: Using RANK() to Rank Employees by Salary

SELECT Name, Department, Salary,
       RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank
FROM employee;

Output

Explanation:

• RANK() function assigns a ranking within each department based on salary (highest salary = rank 1).
• In Finance: Ramesh and Suresh both earn 50,000, so they share rank 1 and next salary (20,000) is assigned rank 3, skipping rank 2.
• In Sales: Deep earns the highest (30,000) -> rank 1 and Pradeep earns less (20,000) -> rank 2.

2.2 DENSE RANK Function

When ranking rows in SQL, ties can sometimes create gaps in the ranking sequence. DENSE_RANK() function is used to avoid this it assigns the same rank to rows with equal values but continues ranking with the next consecutive number, without skipping.

Example: Using DENSE_RANK() to Rank Employees by Salary

SELECT Name, Department, Salary,
       DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_dense_rank
FROM employee;

Output

Explanation:

• DENSE_RANK() works like RANK(), but it ensures the ranking sequence has no gaps.
• In Finance: Ramesh and Suresh both earn 50,000, so they share rank 1 and next salary (20,000) is assigned rank 2 (not rank 3 as in RANK()).
• In Sales: Deep earns the highest (30,000) -> rank 1 and Pradeep earns less (20,000) -> rank 2.

2.3 ROW NUMBER Function

ROW_NUMBER() gives e­ach row a unique number. It numbers rows from one­ to the total rows. The rows are put into groups base­d on their values. Each group is called a partition. In e­ach partition, rows get numbers one afte­r another. No two rows have the same­ number in a partition.

Example: Using ROW_NUMBER() for Unique Row Numbers

SELECT Name, Department, Salary,
       ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_row_no
FROM employee;

Output

Explanation:

• In Finance, Ramesh is row 1, Suresh is row 2, Ram is row 3.
• In Sales, Deep is row 1, Pradeep is row 2.

2.4 PERCENT RANK Function

PERCENT_RANK() shows the relative position of a row compared to others in the same partition. The formula is:

\text{PERCENT\_RANK} = \frac{\text{RANK} - 1}{\text{Total Rows in Partition} - 1}

Example: Using PERCENT_RANK() to Find Relative Salary Position

SELECT Name, Department, Salary,
       PERCENT_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_percent_rank
FROM employee;

Output

Explanation:

• In Finance: Ramesh & Suresh are tied for highest -> 0.00, Ram (lowest) -> 1.00.
• In Sales: Deep (highest) -> 0.00, Pradeep (lowest) -> 1.00.
• PERCENT_RANK helps understand relative standing as a percentage within a department.

Troubleshooting Common Issues with Window Functions

While SQL window functions are incredibly powerful, there are some common pitfalls and challenges that users may encounter:

• Partitioning Error: Ensure that the PARTITION BY clause is used correctly. If no partition is defined, the entire result set is treated as a single window.
• ORDER BY Within the Window: The ORDER BY clause within the window function determines the order of calculations. Always verify that it aligns with the logic of your calculation.
• Performance Considerations: Window functions can be computationally expensive, especially on large datasets. Always ensure that your window functions are optimized and, if necessary, combined with appropriate indexes.