This week I have been completing Leetcode's SQL50; therefore, I thought I would take you through the LAG and LEAD function which I found especially handy for these exercises!
To get the value of a previous/future row you often think you have to join a table to itself. However, you can do this without a join using the LAG or LEAD functions. These functions allow you to create a column or compare to previous/future values without the extra faff!
Both of these functions are window functions. Window functions use everything that is in the view to calculate a result. Because these are window functions, the function will always be followed by OVER. This indicates that the instructions on how to perform the function follows and is used after all window functions in SQL.
The Syntax
The LAG function allows you to get a value from a previous row. The syntax for the LAG function is as follows:
LAG(column_name, offset) OVER ([PARTITION BY column] ORDER BY other_column)
On the contrary, the LEAD function allows you to get a value from a future row. The syntax is the same as the LAG function and is as follows:
LAG(column_name, offset) OVER ([PARTITION BY column] ORDER BY other_column)
The offset is how many rows you wan't to go back to get the value i.e., if it is 1 that is the previous row, if it is 2 that is 2 rows back. If a value isn't specified it will default to 1.
The function will always be followed by OVER and ORDER BY. This is because it needs to know in what order the data is sorted in order to collect the respective values. The other_column is often a date field as this would order the data chronologically.
The 'PARTITION BY' in square brackets is option. What this does is tells it to group the function by that partition column so that it restarts the function.
Use Case: LAG
A potential use case for the LAG function is if you wanted to calculate each year's sales difference to the previous year. Let's say you had the data below in a table called 'Sales'.
To get the previous year's sales you can use LAG in the following syntax:
SELECT *
,LAG(sales,1) OVER (ORDER BY year) as prev_sales
FROM Sales
This syntax says to retrieve all existing fields in the table and a new field called 'prev_sales' that is the previous row of sales when ordered by year. This would return the following:
Next, to calculate the difference you can use the following syntax:
SELECT *
,LAG(sales,1) OVER (ORDER BY year) as prev_sales
,sales - (LAG(sales,1) OVER (ORDER BY year)) as difference
FROM Sales
This syntax retrieves the same as the one above, with the addition of the subtraction of 'prev_sales' from 'sales'. It refers to the calculation rather than the field 'prev_sales' as this does not exist at the point of calculating. This would return the following:
Use Case: LEAD
A potential use case for the LEAD function is to calculate month over month growth. Let's say you had the following data below in a table called 'Revenue'.
To get the following month's revenue you can use LEAD in the following syntax:
SELECT *
,LEAD(revenue,1) OVER (ORDER BY month) as next_rev
FROM Revenue
What this syntax says is to retrieve all existing fields and a new field called 'next_rev' whose values are the revenue for the next row ordered by the month column. This would return the following:
SELECT *
,LEAD(revenue,1) OVER (ORDER BY month) as next_rev
,ROUND(
(LEAD(revenue,1) OVER (ORDER BY month)
/
revenue)
* 100
,2) as growth_percentage
FROM Revenue
This syntax says the same as the previous with the addition of the growth percentage that divides the next_rev by revenue - I have rounded it to 2 decimal places. It would return the following:
