If you’ve ever worked with SQL and needed to perform calculations across a specific set of rows without collapsing them into a single result, then you’ve likely encountered or needed window functions. They are incredibly powerful and can simplify complex queries significantly.
What Are WINDOW FUNCTIONS in SQL?
Window functions in SQL allow us to perform aggregate-like calculations across a defined set of rows related to the current row. Unlike standard aggregate functions (such as SUM() or AVG()), which collapse multiple rows into a single row, window functions return a result for every row in the queried dataset.
The core idea behind window functions is the concept of a “window” of rows, which is defined based on a partitioning and an ordering scheme that determines which rows are included in the calculation for each row.
Basic Syntax of a Window Function
The basic syntax for a window function looks like this:
FUNCTION() OVER (
PARTITION BY column_name
ORDER BY column_name
)Let’s break it down:
- FUNCTION(): This is the window function itself (e.g.,
ROW_NUMBER(),RANK(),SUM(), etc.). - OVER: This keyword defines the window context.
- PARTITION BY: Optional. It groups rows into partitions and runs the function separately on each partition.
- ORDER BY: Optional but commonly used to define the order in which rows are processed.
Best WINDOW FUNCTIONS Examples
To understand window functions better, let’s go through some practical examples with an imaginary table called sales:
| id | employee | department | sales_amount |
|---|---|---|---|
| 1 | Alice | Electronics | 500 |
| 2 | Bob | Electronics | 700 |
| 3 | Charlie | Clothing | 300 |
| 4 | David | Clothing | 400 |
Calculating Row Number
The ROW_NUMBER() function assigns a unique sequential number to each row per partition and ordering.
SELECT
employee, department, sales_amount,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS row_num
FROM sales;
This query assigns a row number to each employee within their department, ordering them by biggest sales first.
Ranking Employees
If we need to rank employees but allow duplicate ranks for identical sales values, we use RANK():
SELECT
employee, department, sales_amount,
RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS rank
FROM sales;
Employees with the same sales_amount will receive the same rank, but the next rank will be skipped if there’s a tie.
Calculating Running Totals
To compute cumulative sales per department, we use SUM() with a window function:
SELECT
employee, department, sales_amount,
SUM(sales_amount) OVER (PARTITION BY department ORDER BY sales_amount) AS running_total
FROM sales;
This allows us to see how sales accumulate per department.
Lead and Lag Functions
To compare the current row with the previous or next row, we use LAG() and LEAD().
SELECT
employee, department, sales_amount,
LAG(sales_amount) OVER (PARTITION BY department ORDER BY sales_amount) AS prev_sales,
LEAD(sales_amount) OVER (PARTITION BY department ORDER BY sales_amount) AS next_sales
FROM sales;
LAG() fetches the value from the previous row, while LEAD() retrieves the next row’s value.
Common Use Cases for WINDOW FUNCTIONS
Window functions have numerous applications, including:
- Ranking users or sales reps within different categories.
- Calculating moving averages, percent changes, or cumulative totals.
- Comparing each row’s value with the previous or next row.
- Selecting the top-N records per group efficiently.
Why Use WINDOW FUNCTIONS Instead of Subqueries?
Using window functions instead of subqueries or self-joins improves both readability and performance. Subqueries can be complex and slow, whereas window functions operate over defined partitions without requiring additional self-referencing queries.
Conclusion
Window functions in SQL are an incredibly powerful tool when you need to perform analytical queries over a subset of data while still retaining row-level granularity. Whether you’re ranking users, calculating running totals, or comparing values across rows, window functions can make your SQL queries cleaner and more efficient.
Other interesting article:
How RECURSIVE CTE works in SQL? Best RECURSIVE CTE examples