LEAD and LAG SQL queries are not difficult to understand
When you want to find day over day, month over month, quarter over quarter, or year over year progress, we use LEAD and LAG SQL queries.
I use MySQL as an illustration. You should be able to run similar queries on other relational databases as well.
Instead of going into technical details, let me show you how to write LEAD and LAG queries using examples
LAG
LAG accesses the value in the row above and makes it available for you to use.
Say we have the following sales table:
Our goal is to find the YoY sales progress.
With a LAG query, we can create a new column (say previous_amount) where we bring the sale amount of the previous year to the current year.
SELECT year, amount,
LAG(amount) OVER(ORDER BY year) AS previous_amount,
FROM sales;
LAG(amount) is actually LAG(amount, 1) where the second parameter is the OFFSET. The default OFFSET value is 1. This simply says that take the value of the immediate row above.
OVER(ORDER BY year) says that we first should order the sale amount by year and then do the LAG operation.
There is another keyword that goes with OVER, which is PARTITION BY: this is used to group the results by another column. We will skip this to keep the example simple.
AS previous_amount simply assigns the column name to the LAG computed field.
The output is:
We can leverage the previous_amount to find the YoY sales progress:
SELECT year, amount,
LAG(amount) OVER(ORDER BY year) AS previous_amount,
amount - LAG(amount) OVER(ORDER BY year) as difference
FROM sales;
The output is:
LEAD
Similar to LAG, LEAD brings another row’s value to the current row. The difference is, instead of getting the previous row’s value to the current row, LEAD brings the following row’s value to the current row.
Let’s say we want to bring the following year’s sale amount to the current year:
SELECT year, amount,
LEAD(amount) OVER(ORDER BY year) AS next_amount,
FROM sales;
The above are the most frequent use of LEAD and LAG keywords.
In a follow-up post, I will go into additional operations used with these keywords: OFFSET and PARTITION BY.
Hope this example-based approach helped you.
Feel free to provide your feedback and share it with those who may benefit from it.
Appreciate your likes to share my writings with a wider audience.
Happy SQLing!