A window function in SQL is a special type of function that performs a calculation across a set of rows that are related to the current row, without grouping or collapsing the rows.
Think of it as looking through a “window” of rows to calculate things like running totals, rankings, moving averages, etc., while keeping each row in the result.
✅ Simple Definition
A window function calculates a value for each row using other rows from the same table that match a condition (called a window or partition).
🔧 Syntax
function_name(column)
OVER (
PARTITION BY column
ORDER BY column
)
- PARTITION BY → divides the data into groups (optional)
- ORDER BY → defines the order to apply the function (optional)
⭐ Common Window Functions
1. ROW_NUMBER()
Gives a unique number for each row.
ROW_NUMBER() OVER (ORDER BY salary)
2. RANK() / DENSE_RANK()
Assigns ranking based on a column.
RANK() OVER (ORDER BY marks DESC)
3. SUM() (running/ cumulative total)
SUM(amount) OVER (ORDER BY date)
4. AVG() (moving average)
AVG(sales) OVER (ORDER BY month)
5. LAG() / LEAD()
Access previous or next row value.
LAG(salary) OVER (ORDER BY id)
LEAD(salary) OVER (ORDER BY id)
🧠 Example to Understand Easily
Table: Sales
| id | month | amount |
|---|---|---|
| 1 | Jan | 100 |
| 2 | Feb | 200 |
| 3 | Mar | 150 |
Running total using a window function:
SELECT month, amount,
SUM(amount) OVER (ORDER BY month) AS running_total
FROM Sales;
Output:
| month | amount | running_total |
|---|---|---|
| Jan | 100 | 100 |
| Feb | 200 | 300 |
| Mar | 150 | 450 |
👉 Rows stay untouched, but extra calculated values are added.
🎯 When to Use Window Functions
Use them when you need:
- Running totals
- Moving averages
- Ranking
- Comparing rows
- Percentage of total
- Previous/next row values
Sure! Here is a deeper, clearer, and more practical explanation of SQL window functions with more examples.
⭐ Window Functions – Detailed Explanation
A window function performs calculations without grouping or reducing rows, unlike GROUP BY.
It adds extra computed columns while keeping the original rows.
🔍 Why window functions are powerful
They allow you to:
- Calculate running totals
- Calculate moving averages
- Generate rankings
- Calculate percentages
- Compare rows using LAG() and LEAD()
- Find first/last value in each group
- Perform cumulative calculations within groups
🧱 Structure of a Window Function
function_name(column)
OVER (
PARTITION BY col1
ORDER BY col2
ROWS BETWEEN ... -- optional (window frame)
)
Let’s break it down:
✔ PARTITION BY
Divides rows into groups (like GROUP BY but without collapsing).
✔ ORDER BY
Defines the order rows are processed.
✔ ROWS BETWEEN
Defines how many rows to look at (critical for moving averages & running totals).
📘 1. Ranking Functions
1. ROW_NUMBER()
Gives each row a unique number.
ROW_NUMBER() OVER (ORDER BY salary DESC)
2. RANK()
Same salary = same rank, but gaps occur.
RANK() OVER (ORDER BY salary DESC)
3. DENSE_RANK()
Same salary = same rank, no gaps.
DENSE_RANK() OVER (ORDER BY salary DESC)
Example Output
| salary | row_number | rank | dense_rank |
|---|---|---|---|
| 5000 | 1 | 1 | 1 |
| 4000 | 2 | 2 | 2 |
| 4000 | 3 | 2 | 2 |
| 3000 | 4 | 4 | 3 |
📘 2. Aggregate Window Functions
These use SUM(), AVG(), MIN(), MAX(), COUNT() with OVER().
Running Total (Cumulative Sum)
SUM(amount) OVER (ORDER BY date) AS running_total
Running Average
AVG(amount) OVER (ORDER BY date)
Using PARTITION BY (reset per group)
SUM(sales)
OVER (PARTITION BY region ORDER BY month)
This gives cumulative sales per region.
📘 3. Analytic Functions: LAG() and LEAD()
LAG() → previous row
LEAD() → next row
Used for comparing rows.
LAG(salary, 1) OVER (ORDER BY id) AS previous_salary
LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
Example:
| id | salary | previous_salary |
|---|---|---|
| 1 | 1000 | NULL |
| 2 | 1200 | 1000 |
| 3 | 1500 | 1200 |
📘 4. FIRST_VALUE() and LAST_VALUE()
FIRST_VALUE()
Gets the first value in the window.
FIRST_VALUE(salary) OVER (ORDER BY salary DESC)
LAST_VALUE()
Gets last value (careful—needs framing):
LAST_VALUE(salary)
OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
📘 5. NTILE()
Divides rows into equal groups (quartiles, percentiles, etc.).
NTILE(4) OVER (ORDER BY salary)
This divides the data into 4 quartiles.
📘 6. Window Frame (Advanced)
Used for moving averages, sliding windows, etc.
Example: Last 3 months moving average
AVG(sales) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
Meaning:
- Current month
- Previous 2 months
🧠 Summary Table
| Purpose | Function |
|---|---|
| Ranking | ROW_NUMBER(), RANK(), DENSE_RANK() |
| Running totals | SUM() OVER |
| Moving averages | AVG() OVER with ROWS BETWEEN |
| Compare previous/next row | LAG(), LEAD() |
| First/Last value | FIRST_VALUE(), LAST_VALUE() |
| Percentile groups | NTILE() |
0 Comments