Window Function

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:  

1. Ranking Rows 

2. Dense Rank 

3. Running Totals 

4. Moving Averages 

5. Comparing Row Values 

6. 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: 

2. LEAD Function    

3. Retrieves data from a future row in the result set. 

4. 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.