Window Functions in SQL: A Complete Guide

Window functions are powerful tools in SQL that allow you to perform calculations across a subset of rows related to the current row, without collapsing the data into a single result set. Unlike aggregate functions like SUM() or COUNT(), which return a single value for a group of rows, window functions retain the individual rows while adding additional analytical insights.

In this guide, we’ll explain what window functions are, their common use cases, and walk through examples of popular functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG().

What Are Window Functions?

A window function performs a calculation across a set of rows (called a window) defined by a PARTITION BY clause. This "window" can be the entire dataset or a subset of it. The results of window functions are returned for each row in the result set.

Syntax:

function_name(expression) OVER (
    [PARTITION BY column_name(s)]
    [ORDER BY column_name(s)]
)

• PARTITION BY: Divides the result set into subsets (like groups). Optional.
• ORDER BY: Specifies the order of rows within each partition.
• Function Name: Determines the type of calculation (e.g., ranking, summing).

Key Window Functions

1. ROW_NUMBER()

Assigns a unique number to each row within a partition, starting at 1 for the first row.

Use Case: Generate unique row numbers for each row in a dataset or partition.

Example: Rank employees by hire date within each department.

SELECT department_id, employee_name, hire_date,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS row_number
FROM employees;

Explanation:

• PARTITION BY department_id: Groups rows by department.
• ORDER BY hire_date: Assigns row numbers based on hire date within each department.

2. RANK()

Assigns a rank to each row within a partition. If there are ties, the same rank is given, and the next rank is skipped.

Use Case: Determine rankings with ties in a competition or scores.

Example: Rank employees by salary within each department.

SELECT department_id, employee_name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Explanation:

• PARTITION BY department_id: Groups rows by department.
• ORDER BY salary DESC: Ranks employees by descending salary within each department.

3. DENSE_RANK()

Similar to RANK(), but without skipping ranks for ties.

Use Case: Assign continuous ranks even when there are ties.

Get Tilak Mudgal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Example:

SELECT department_id, employee_name, salary,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

Difference from RANK(): If two employees in department 1 have the same salary, both will be ranked 1, and the next rank will be 2 (not 3).

4. NTILE(n)

Divides rows into n equal-sized groups and assigns a bucket number to each row.

Use Case: Distribute rows into quantiles (e.g., quartiles, deciles).

Example: Divide employees into four salary quartiles.

SELECT employee_name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Explanation: Rows are divided into four groups, with each group assigned a quartile (1 to 4).

5. LEAD() and LAG()

• LEAD(): Accesses data from the next row in the result set.
• LAG(): Accesses data from the previous row in the result set.

Use Case: Compare a value in one row to the previous or next row.

Example: Compare each employee’s salary to the next highest salary.

SELECT employee_name, salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

Explanation:

• LEAD(salary): Returns the salary from the next row in descending order.

6. SUM() (or Other Aggregate Functions with a Window)

Calculates cumulative or running totals across a window.

Use Case: Calculate cumulative sales totals by month.

Example:

SELECT employee_name, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS cumulative_salary
FROM employees;

Explanation:

Calculates a running total of salaries within each department.

Best Practices for Window Functions

1. Understand the Dataset: Ensure you partition and order rows appropriately for meaningful results.
2. Avoid Overusing: Window functions can be computationally expensive; optimize queries with indexes.
3. Combine Functions: Use multiple window functions together for advanced analytics.

Conclusion

Window functions are invaluable for data analysis, providing deep insights while preserving individual rows in the result set. Whether ranking employees, calculating running totals, or comparing rows, window functions unlock new possibilities for SQL queries.