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: It divides the data into groups using PARTITION BY.
• ORDER BY: It specifies the order of rows within each group using ORDER BY.

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;

• window_function: Aggregate or ranking function (SUM(), AVG(), ROW_NUMBER(), etc.)
• column_name1: Column(s) to display
• column_name2: Column used by the window function
• column_name3: Column for grouping (PARTITION BY)
• column_name4: Column for ordering (ORDER BY)
• new_column: Alias for the window function result
• table_name: Table to select data from

Types of Window Functions in SQL

SQL window functions are mainly of two types: aggregate window functions and ranking window functions.

Example: First, we create a demo table Employee, and now we will apply window functions on it.

1.Aggregate Window Functions

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(), we will calculate the average salary within each department.

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

Output:

• For Finance: (50,000 + 50,000 + 20,000) ÷ 3 = 40,000.
• For Sales: (30,000 + 20,000) ÷ 2 = 25,000.
• This average value is displayed for each employee belonging to 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.

RANK() Function

RANK() functions are used to assign ranks to rows within a group based on a specific order. Ranking functions are commonly used to organize and analyze data. Some common ranking functions include:

Example: Using RANK(), we will rank employees by salary, allowing gaps in ranks when salaries are equal.

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

Output:

• RANK() function assigns a ranking within each department based on salary (highest salary = rank 1).
• Finance: Andrew and Brian both earn 50,000, so both get rank 1. The next salary is 20,000, so it gets rank 3 (rank 2 is skipped).
• Sales: Daniel earns 30,000, so he gets rank 1. Ethan earns 20,000, so he gets rank 2.

DENSE RANK() Function

DENSE_RANK() gives the same rank to rows with equal values. It then continues with the next number without skipping, keeping the ranking sequence continuous.

Example: Using DENSE_RANK(), we will rank employees by salary without skipping ranks.

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

Output:

• DENSE_RANK() works like RANK(), but it ensures the ranking sequence has no gaps.
• Finance: Andrew and Brian both earn 50,000, so they get rank 1. The next salary (20,000) is assigned rank 2 (no gap).
• Sales: Daniel earns the highest salary (30,000) and gets rank 1.Ethan earns 20,000 and gets rank 2.

ROW NUMBER() Function

ROW_NUMBER() gives a unique number to each row in the result set. It increments by 1 for every row, even if values are the same, so no two rows have the same number.

Example: Using ROW_NUMBER(), we will assign a unique number to each row based on salary order.

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

Output:

• In Finance, Andrew is row 1, Brian is row 2, Charles is row 3.
• In Sales, Daniel is row 1, Ethan is row 2.

PERCENT RANK() Function

PERCENT_RANK() shows where a row stands compared to others in the same group. The formula is:

PERCENT RANK() = (RANK - 1)/(Total Rows in Partition - 1)

Example: Using PERCENT_RANK(), we will find the relative salary position of each employee within a department.

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

Output:

• In Finance: Andrew & Brian are tied for highest = 0.00, Charles (lowest) = 1.00.
• In Sales: Daniel (highest) = 0.00, Ethan (lowest) = 1.00.
• Each value shows the employee’s relative position within the department.

Fixing Window Function Issues

This helps to identify and fix problems like incorrect partitioning, wrong order, or slow performance when using window functions.

• Partition carefully: Without Partition by, the whole table is treated as one group.
• Check Order by : It controls the calculation order in the window.
• Optimize performance: Window functions can be slow on large datasets; use indexes if needed.