Window Function
- Ray Minds
- Apr 1
- 4 min read
By:- Shivanee Sharma Introduction
Window functions in SQL help you to analyse rows while keeping the data intact. Unlike aggregate functions (which combine rows into a one result), window functions work on rows. It connects to the current row and adds new columns without collapsing the original data.
In this blog, we will be covering below topics in the aspect of Windows function:
Ranking Rows
Dense Rank
Running Totals
Moving Averages
Comparing Row Values
Lag() and Lead()
Use Case of Window Functions
1. Ranking Rows
Ranking functions return a ranking value for each row in a dataset. Some rows might receive the same value as other rows. Ranking functions skip numbers when there’s a tie.
Example:
Imagine you have a list of salespeople, and you want to rank them based on their sales within each region. By using a window function, you can assign ranks without affecting the structure of the data.
SELECT SalesPerson_ID, Region, SalesAmount,
RANK()OVER(PARTITION BY Region ORDER BY SalesAmount DESC) AS Rank
FROM Sales_Data;
SQL Output:

This gives each salesperson a rank, specific to their region.
2. Dense Rank
It is like giving ranks to rows, but with a twist:
It ensures there are no gaps between ranks, even if some rows have same value.
Dense Rankings shouldn’t skip numbers even if there are ties.
Example:
SELECT Salesperson_ID, Region, SalesAmount,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS Rank
FROM sales_data;
SQL Output:

3. Calculating Running Totals. It’s a common Calculating method that shows how a value grows over time. It is also known as ‘Cumulative total’ or ‘Partial sum’. It is a process of adding each value in a sequence to the sum of the previous values.
We can calculate Running Totals with 2 types 1. Rows Between Unbounded Preceding and Current Row 2. Range Between Unbounded Preceding and Current Row
Example: 1. Rows Between Unbounded Preceding and Current Row
Operates on a Fixed number of rows.
The window includes all rows from the first row in the partition up to the current row.
Example: It calculates a running total by summing row by row in order.
SELECT SalesPerson_ID, SalesDate, SalesAmount
SUM(SalesAmount) OVER(PARTITION BY SalesPerson_ID
ORDER BY SalesDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Running_Total
FROM Sales_Data;
SQL Output:

2. Range Between Unbounded Preceding and Current Row
Operates on a range of values, not rows.
If there are duplicate values in the ORDER BY column, it considers all of them together.
The default is if you don’t specify ROWS or RANGE.
SELECT SalesPerson_ID, SalesDate, SalesAmount,
SUM(SalesAmount) OVER(PARTITION BY SalesPerson_ID
ORDER BY SalesDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Running_Total_RANGE
FROM Sales_Data;
SQL Output:

In this example:
Rows Between adds each row one by one.
Range Between adds all rows with the same SalesDate at once.
4. Finding Moving Averages A moving average calculates the average of values within a specific time, which "moves" as time progresses. Here, it calculates the average of sales for each salesperson over a 2-day period.
Example:
SELECT SalesPerson_ID, SalesDate, SalesAmount,
AVG(salesAmount) OVER (PARTITION BY salesperson_id ORDER BY SalesDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWS)
AS Moving_Avg
FROM Sales_Data;
SQL Output:

So, for each sale date, the average is calculated based on the sales amount for that day and the two previous days.
2 Preceding: Includes the two sales entries just before the current row.
Current Row: Include the current row itself.
5. Comparing Row Values When you're looking for differences between rows, window functions make this simple. For example, you could calculate how sales differ from one day to the next.
Example:
SELECT SalesPerson_ID, SalesDate, SalesAmount,
SalesAmount - LAG(SalesAmount) OVER (PARTITION BY salesperson_id
ORDER BY SalesDate) AS Difference_from_previous_day
FROM Sales_Data;
SQL Output:

This shows the daily sales change for each salesperson.
6. LAG() and LEAD() Function
The LAG and LEAD functions in SQL are window functions used to access data from previous or next rows within the same result set without using self-joins.
1. LAG() Function
Retrieves data from a previous row in the result set.
Commonly used for calculating differences between consecutive rows.
SELECT SalesPerson_ID, SalesDate, SalesAmount,
LAG(SalesAmount, 1, 0)
OVER (PARTITION BY SalesPerson_ID ORDER BY SalesDate)
AS Previous_Sales_Amount
FROM Sales_Data;
SQL Output:

LEAD Function
Retrieves data from a future row in the result set.
Helps in trend analysis or comparing the current row with the next one.
SELECT SalesPerson_ID, SalesDate, SalesAmount,
LEAD(SalesAmount, 1, 0)
OVER (ORDER BY SalesAmount) AS next_Amount FROM Sales_Data;
SQL Output:

Conclusion
Window functions are powerful because they simplify complex analyses. You can achieve things like ranking, cumulative totals, and trends directly in your SQL queries. They're your go-to for adding a layer of depth and insight to your data.
The OVER clause is the main part of window functions. It decides which rows the function should look at.
PARTITION BY → Divides data into groups.
ORDER BY → Sorts the data in each group.
This helps functions like SUM(), AVG(), ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD() work in a flexible way. You can analyse data in detail without changing its original form.
Comments