SQL window functions are one of the most powerful tools for performing calculations across a defined range of rows while maintaining access to individual row-level details. Unlike aggregate functions that collapse rows into a single value, window functions allow you to perform calculations over a subset of rows (a window) while preserving the original row structure.
This in-depth guide explores what window functions are, how they work, different types, use cases, and practical examples.
What Are SQL Window Functions?
A window function operates on a subset of rows (called a window) and returns a value for each row based on calculations across that subset. These functions use the OVER() clause, which defines how the rows in the window are partitioned and ordered.
Unlike GROUP BY aggregation, window functions do not collapse rows, meaning you can compute rankings, running totals, and moving averages while retaining individual row details.
Key Components of Window Functions
A SQL window function is composed of:
- Function Type – The specific computation performed (e.g.,
RANK(),SUM(),LAG()). - OVER() Clause – Defines the window for calculation.
- PARTITION BY – Splits rows into groups for separate calculations.
- ORDER BY – Determines the order of rows within the window.
- ROWS/RANGE – Defines the subset of rows to consider.
Basic Syntax
SELECT column_name, window_function() OVER(
PARTITION BY column_name
ORDER BY column_name
ROWS BETWEEN n PRECEDING AND m FOLLOWING
) AS computed_value
FROM table_name;
Types of SQL Window Functions
SQL window functions can be categorized into four main types:
- Ranking Functions (Assign ranks to rows)
- Aggregate Window Functions (Perform cumulative calculations)
- Offset (Lag/Lead) Functions (Access previous or next rows)
- Statistical Window Functions (Provide percentiles, NTILE bucketing)
Let’s explore each category with examples.
1. Ranking Functions
Ranking functions assign unique or non-unique ranks to rows based on the specified order.
1.1 RANK()
Assigns a rank to each row. Rows with equal values receive the same rank, and the next rank skips numbers.
Example: Rank Employees by Salary
SELECT employee_id, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
- Same salaries get the same rank.
- Next rank is skipped (e.g., ranks go 1, 2, 2, 4).
1.2 DENSE_RANK()
Works like RANK() but does not skip rank values.
SELECT employee_id, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
- If two employees have the same salary, they get the same rank.
- The next rank does not skip a number (e.g., ranks go 1, 2, 2, 3).
1.3 ROW_NUMBER()
Assigns a unique number to each row, even for ties.
SELECT employee_id, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
- Every row gets a unique number, even for identical values.
2. Aggregate Window Functions
Aggregate functions return cumulative calculations across a window of rows.
2.1 SUM()
Computes a running total.
SELECT order_id, customer_id, order_date, total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;
- Calculates a cumulative sum per customer over time.
2.2 AVG()
Computes a moving average.
SELECT product_id, sale_date, sales,
AVG(sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;
- Averages sales over a 3-day window (
2 preceding + current row).
2.3 COUNT()
Counts occurrences without collapsing rows.
SELECT customer_id, purchase_category,
COUNT(*) OVER (PARTITION BY customer_id) AS purchase_count
FROM purchases;
- Returns the number of purchases per customer.
3. Offset (Lag/Lead) Functions
Offset functions help access previous or next row values.
3.1 LAG()
Retrieves the value from the previous row.
SELECT employee_id, department, salary,
LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary
FROM employees;
- Returns the salary of the previous employee in the department.
3.2 LEAD()
Retrieves the value from the next row.
SELECT employee_id, department, salary,
LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary
FROM employees;
- Returns the salary of the next employee in the department.
4. Statistical Window Functions
Used for percentiles, quantiles, and distribution analysis.
4.1 NTILE()
Divides rows into equal-sized buckets.
SELECT customer_id, total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customers;
- Divides customers into four quartiles based on spending.
4.2 PERCENT_RANK()
Returns a percentile rank from 0 to 1.
SELECT employee_id, salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_rank
FROM employees;
- Returns the relative rank percentage.
Key Differences Between Window Functions and Aggregate Functions
| Feature | Window Functions | Aggregate Functions |
|---|---|---|
| Row Retention | Retains all rows | Collapses rows into groups |
| Scope | Works over a defined “window” | Works over entire dataset or group |
| Usage | Ranking, running totals, lag/lead | Sum, average, count per group |
When to Use Window Functions
Ranked Reports: Sorting employees, customers, or products based on sales, revenue, etc. Running Totals: Computing cumulative values like revenue growth over time. Comparing Rows: Accessing previous/next values with LAG() and LEAD(). Statistical Distributions: Dividing data into quantiles with NTILE().
Final Thoughts
SQL window functions provide flexibility, efficiency, and analytical power by allowing you to perform row-wise calculations while keeping individual row details.
Mastering these functions optimizes query performance, simplifies complex reporting, and enhances analytical capabilities in SQL-based applications.
Try them out today in your database system!
Lastly, here are the very very helpful windows function cheat sheet from learnsql.com you will find very helpful, happy sqling!
