Value Window Functions In SQL

In this article, I will explain value window functions such as LEAD( ), LAG( ), FIRST_VALUE( ), and LAST_VALUE( ).

What is Value Window Functions?

Value Window Functions in SQL return a single value for each row based on a group of related rows, known as a window. They allow you to calculate values for each row by comparing them with others simply and efficiently, without complex joins.

Simple Definition of Each Functions

• LEAD( ) - gets the values from the next row
• LAG( ) - gets the values from the previous row
• FIRST_VALUE( ) - gives the first value in the group
• LAST_VALUE( ) - gives the last value in the group

Use Cases of this Functions

It has multiple uses, such as trend analysis, which tracks changes in data over time, such as stock prices or sales growth. These functions also allow users to compare the current row with the previous or next row to gain valuable insights.

Using functions like FIRST_VALUE() and LAST_VALUE(), we can identify important details, such as the earliest or latest transaction, and determine the first and last values within a dataset. Additionally, performance analysis can be performed to compare metrics over time, such as month-over-month growth or employee performance.

Beyond these examples, value window functions have many applications for performing advanced analytics efficiently.

Now we can see each function with examples

LEAD( ) AND LAG( )

LAG( ) function accesses a value from the previous row within a window, and the LEAD( ) function accesses a value from the next row within the window.

So these two functions are one-to-one opposites of it.

Syntax of these functions

SELECT 
  SaleID, 
  Product, 
  SalesAmount, 
  LAG(SalesAmount, 1) OVER (PARTITION BY Product ORDER BY SaleDate) AS PreviousSaleAmount,
  LEAD(SalesAmount, 1) OVER (PARTITION BY Product ORDER BY SaleDate) AS NextSaleAmount
FROM `Sales_data.Sales`;        

This retrieves SalesAmount the previous and next sales records for each product.

FIRST_VALUE( ) AND LAST_VALUE ( )

FIRST_VALUE ( ) accesses a value from the first row within a window, and LAST_VALE( ) accesses a value from the last row within a window.

SELECT 
    Product,
    SaleDate,
    SalesAmount,
    FIRST_VALUE(SalesAmount) OVER (PARTITION BY Product ORDER BY SaleDate ASC) AS FirstSaleAmount,
    LAST_VALUE(SalesAmount) OVER (PARTITION BY Product ORDER BY SaleDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleAmount
FROM `Sales_data.Sales`        

It will return the first and last value for each product.

Why we are using the "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" clause in the LAST_VALUE( ) function?

The clause "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" is used with the LAST_VALUE function to ensure that the entire window (all rows in the partition) is considered when determining the last value, regardless of the current row being processed.

By default, the window frame for SQL window functions includes only the rows from the start of the partition up to the current row, unless a specific frame is defined. As a result, if the frame is not specified, the LAST_VALUE function would incorrectly return the value of the current row instead of the actual last value in the partition.

We have reached the conclusion of this article, which also signifies the end of the Windows functions series in SQL. In my next article, I will share my new insights and learnings.

If you're reading this as the final article in my Windows function series, I will include all the links to the previous articles in the comments section. Feel free to check them out! :)