Home › SQL Tutorial › SQL Window Functions

SQL Window Functions

⏱ 9 min read Updated: 13 Nov 2026

Window functions in SQL allows us to perform calculations across a group of related rows without combining them into a single result. They allow us to calculate values such as totals, averages, rankings, and running totals while keeping individual rows in the output.

Syntax of Window Functions in SQL

It has the following syntax.

SELECT column_name,       window_function(column_name)       OVER (           [PARTITION BY column_name]           [ORDER BY column_name]       ) AS result_column FROM table_name;

Explanation of the syntax:

  • SELECT: Specifies the columns to retrieve from the table.
  • window_function(): Defines the function used for calculation or ranking.
  • OVER(): Defines the window of rows used by the function.
  • PARTITION BY: Divides rows into separate groups.
  • ORDER BY: Specifies the order of rows within each group.
  • AS: Assigns a name to the calculated result.
  • FROM: Specifies the source table.

Important Clauses Used in Window Functions

There are three important clauses commonly used with SQL window functions.

1. OVER Clause

The OVER() clause defines the set of rows on which a window function performs its calculation.

Example:

SELECT name, salary,       AVG(salary) OVER() AS avg_salary FROM employee;

This query calculates the average salary of all employees and displays the same average value alongside every employee.

2. PARTITION BY Clause

The PARTITION BY clause divides the result set into separate groups. The window function performs its calculation independently within each group.

Example:

SELECT name, department, salary,       AVG(salary) OVER(           PARTITION BY department       ) AS avg_salary FROM employee;

This query calculates the average salary separately for each department.

3. ORDER BY Clause

The ORDER BY clause specifies the order in which rows are processed within a window.

Example:

SELECT name, salary,       ROW_NUMBER() OVER(           ORDER BY salary DESC       ) AS row_num FROM employee;

This query assigns row numbers based on salary, starting with the highest salary.

Types of Window Functions in SQL

SQL window functions can be broadly classified into two main categories:

  1. Aggregate Window Functions
  2. Ranking Window Functions

Let's understand each type with examples.

1. Aggregate Window Functions

Aggregate window functions perform calculations on a group of rows and return the result for each individual row. Some commonly used aggregate window functions are:

FunctionDescription
SUM()It calculates the total of values.
AVG()It calculates the average of values.
COUNT()It counts the number of rows.
MAX()It returns the maximum value.
MIN()It returns the minimum value.

Example of Aggregate Window Functions

First, let's create an Employee table to understand aggregate window functions.

CREATE TABLE employee (    id INT,    name VARCHAR(50),    department VARCHAR(50),    salary INT );

Insert some sample records:

INSERT INTO employee VALUES (1, 'Andrew', 'Finance', 50000), (2, 'Brian', 'Finance', 50000), (3, 'Charles', 'Finance', 20000), (4, 'Daniel', 'Sales', 30000), (5, 'Ethan', 'Sales', 20000);

The Employee table contains the following data:

idnamedepartmentsalary
1AndrewFinance50000
2BrianFinance50000
3CharlesFinance20000
4DanielSales30000
5EthanSales20000

AVG() Function

The AVG() function calculates the average value of a specified column within a window.

Example: Calculate the average salary of employees in each department.

SELECT name, department, salary,       AVG(salary) OVER(           PARTITION BY department       ) AS avg_salary FROM employee;

Output:

namedepartmentsalaryavg_salary
AndrewFinance5000040000
BrianFinance5000040000
CharlesFinance2000040000
DanielSales3000025000
EthanSales2000025000

SUM() Function

The SUM() function calculates the total of values within a specified window.

Example: Calculate the total salary of employees in each department.

SELECT name, department, salary,       SUM(salary) OVER(           PARTITION BY department       ) AS total_salary FROM employee;

Output:

namedepartmentsalarytotal_salary
AndrewFinance50000120000
BrianFinance50000120000
CharlesFinance20000120000
DanielSales3000050000
EthanSales2000050000

COUNT() Function

The COUNT() function counts the number of rows within a specified window.

Example: Count the total employees in each department.

SELECT name, department,       COUNT(*) OVER(           PARTITION BY department       ) AS total_employees FROM employee;

Output:

namedepartmenttotal_employees
AndrewFinance3
BrianFinance3
CharlesFinance3
DanielSales2
EthanSales2

MAX() Function

The MAX() function returns the highest value within a specified window.

Example: Find the highest salary in each department.

SELECT name, department, salary,       MAX(salary) OVER(           PARTITION BY department       ) AS highest_salary FROM employee;

Output:

namedepartmentsalaryhighest_salary
AndrewFinance5000050000
BrianFinance5000050000
CharlesFinance2000050000
DanielSales3000030000
EthanSales2000030000

MIN() Function

The MIN() function returns the lowest value within a specified window.

Example: Find the lowest salary in each department.

SELECT name, department, salary,       MIN(salary) OVER(           PARTITION BY department       ) AS lowest_salary FROM employee;

Output:

namedepartmentsalarylowest_salary
AndrewFinance5000020000
BrianFinance5000020000
CharlesFinance2000020000
DanielSales3000020000
EthanSales2000020000

2. Ranking Window Functions

Ranking window functions assign numbers or ranks to rows based on a specified order.

They are useful for finding top-performing employees, ranking students, comparing sales performance, and analyzing records.

Common ranking window functions include:

FunctionDescription
ROW_NUMBER()Assigns a unique number to each row.
RANK()Assigns the same rank to equal values and skips subsequent ranks.
DENSE_RANK()Assigns the same rank to equal values without skipping ranks.
PERCENT_RANK()Calculates the relative rank of a row between 0 and 1.

Let's understand these functions with examples.

1. RANK() Function

The RANK() function assigns a rank to each row based on the specified order. If two or more rows have the same value, they receive the same rank, and the next rank is skipped.

Example: Rank employees based on their salaries within each department.

SELECT name, department, salary,       RANK() OVER(           PARTITION BY department           ORDER BY salary DESC       ) AS emp_rank FROM employee;

Output:

namedepartmentsalaryemp_rank
AndrewFinance500001
BrianFinance500001
CharlesFinance200003
DanielSales300001
EthanSales200002

2. DENSE_RANK() Function

The DENSE_RANK() function assigns ranks to rows based on their values. If multiple rows have the same value, they receive the same rank. However, it does not skip the next rank.

Example: Rank employees based on salary without skipping rank numbers.

SELECT name, department, salary,       DENSE_RANK() OVER(           PARTITION BY department           ORDER BY salary DESC       ) AS emp_dense_rank FROM employee;

Output:

namedepartmentsalaryemp_dense_rank
AndrewFinance500001
BrianFinance500001
CharlesFinance200002
DanielSales300001
EthanSales200002

3. ROW_NUMBER() Function

The ROW_NUMBER() function assigns a unique sequential number to each row based on the specified ordering.

Unlike RANK() and DENSE_RANK(), it does not assign the same number to duplicate values.

Example: Assign a unique row number to employees based on their salaries.

SELECT name, department, salary,       ROW_NUMBER() OVER(           PARTITION BY department           ORDER BY salary DESC, id       ) AS emp_row_no FROM employee;

Output:

namedepartmentsalaryemp_row_no
AndrewFinance500001
BrianFinance500002
CharlesFinance200003
DanielSales300001
EthanSales200002

4. PERCENT_RANK() Function

The PERCENT_RANK() function calculates the relative ranking of a row within a partition. Its result ranges from 0 to 1.

The formula is:

PERCENT_RANK() = (RANK - 1) / (Total Rows - 1)

Example: Calculate the relative salary ranking of employees within each department.

SELECT name, department, salary,       PERCENT_RANK() OVER(           PARTITION BY department           ORDER BY salary DESC       ) AS emp_percent_rank FROM employee;

Output:

namedepartmentsalaryemp_percent_rank
AndrewFinance500000.00
BrianFinance500000.00
CharlesFinance200001.00
DanielSales300000.00
EthanSales200001.00

Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()

These three functions are commonly used for ranking records in SQL. However, their behavior is different when duplicate values are present.

Consider the following salary values:

EmployeeSalary
Andrew50000
Brian50000
Charles20000
Daniel10000

The ranking results are:

EmployeeRANK()DENSE_RANK()ROW_NUMBER()
Andrew111
Brian112
Charles323
Daniel434

Main Differences:

  • RANK(): Assigns the same rank to duplicate values and skips the next rank.
  • DENSE_RANK(): Assigns the same rank to duplicate values without skipping ranks.
  • ROW_NUMBER(): Assigns a unique sequential number to every row.

Difference Between Window Functions and GROUP BY

Window functions and GROUP BY are both used to perform calculations on data, but they produce different results.

Window FunctionsGROUP BY
Retains individual rows.Combines rows into groups.
Uses the OVER() clause.Uses the GROUP BY clause.
Can calculate rankings and running totals.Commonly calculates group-level aggregates.
Displays calculated values alongside original rows.Returns one result row per group.

Example using GROUP BY:

SELECT department,       AVG(salary) AS avg_salary FROM employee GROUP BY department;

This query returns one row for each department.

Example using a Window Function:

SELECT name, department, salary,       AVG(salary) OVER(           PARTITION BY department       ) AS avg_salary FROM employee;

This query displays every employee along with their department's average salary.

Common Mistakes When Using Window Functions

While working with SQL window functions, beginners may encounter some common mistakes.

1. Incorrect PARTITION BY Clause

Using the wrong partition column can produce incorrect calculations. Always select the appropriate column for dividing records into groups.

2. Missing ORDER BY Clause

Ranking functions usually require an ORDER BY clause to define the ranking order. Without it, the ranking may not represent the intended result.

3. Confusing RANK() and DENSE_RANK()

Both functions assign the same rank to duplicate values, but RANK() skips subsequent ranks whereas DENSE_RANK() does not.

4. Using Window Functions Directly in WHERE

In many SQL databases, window functions cannot be used directly in the WHERE clause. Use a CTE or subquery to filter their results.

Example:

WITH EmployeeRank AS (    SELECT name, department, salary,           RANK() OVER(               PARTITION BY department               ORDER BY salary DESC           ) AS emp_rank    FROM employee ) SELECT * FROM EmployeeRank WHERE emp_rank = 1;

This query returns the highest-paid employee or employees in each department, including ties.

5. Ignoring Performance

Window functions may require sorting and processing large sets of rows. For better performance, use appropriate indexes, filter unnecessary records, and review the query execution plan.