Look Ahead, Look Behind: The Power of LEAD and LAG Window Functions in SQL
In SQL, the LEAD and LAG window functions are powerful tools that allow you to access data from preceding or following rows in your dataset, within the scope of a partition. They are often used in time series analysis, data comparisons, and trend analysis.
“Get ready to supercharge your interview prep and stand out!”
What is a LEAD function?
The “ Lead ’’ function retrieves data from the next row relative to the current row in the result set.
What is a LAG function?
The “Lag” function retrieves data from the previous row relative to the current row in the result set.
Syntax :
LEAD (column_Name, offset, Default _Value) OVER (ORDER BY col1,col2,….)
LAG (column_Name, offset, Default_Value) OVER (ORDER BY col1, col2,….)
Let’s Understand What Each Parameters Are In LEAD/LAG Syntax:
column_Name:- The column to retrieve the value from.
Offset:-Number of rows to lead and lag.
Default_Value:-The default value to return if the number of rows to lead or lag goes beyond the first row or last row in the table or partition. If a default value is not specified, null is returned.
Example:- Here’s an example of a simple sales data table that can be used to demonstrate the functionality of LEAD and LAG window functions in SQL.
Sales Data Table:
Using Lead To Get Next-Day sales:-
Result of the Lead Query :
In this output, the next_sales column shows the sales amount for the next available date within the same partition done on the product. When there is no subsequent/next row, hence the result is NULL, otherwise this row is filled by a subsequent sales_amount value.
Using Lag To Get The Previous Day's Sales
Result of the Lag Query:
In this output, the previous_sales column shows the sales amount for the previously available date within the same partition. When there is no previous row, hence the result is NULL otherwise this row is filled by a previous sales_amount value.
Practical Use Cases:-
Trend Analysis: Compare the values of the current row with the previous or future rows.
Stock Market Data:- Analyse daily price movements by comparing current prices to past or future prices.
Sales Reporting:- Compare current sales data with the previous or next day, week, or month.
Conclusion :
Both LEAD and LAG functions are invaluable when you need to perform calculations involving the previous or next row, particularly in time-series data. they make your queries more efficient and easier to read by eliminating the need for self-joins.
