How to use the SQL LEAD and LAG Functions
From Theory to Practice, fully explained.
In a previous article, I described how Window Functions work and how they made MySQL finally capable of data analysis work (although there are better methods).
In this article, you will (re)discover two new Window Functions: the LAG() and LEAD() functions.
They are unique in that they are sometimes called “positional functions”, and they are beneficial for making dashboards where you want to compare a value to its previous value or the next one!
Maybe you’ve already heard about the “YoY comparison” or used the comparison to the previous period function in the Data Studio Map Widget?
This is the kind of problem we will try to solve with SQL 😇
The problem of the day
Your client asks you to display monthly, quarterly, and yearly sales and profits in his tracking dashboard.
You can test the examples in this article by downloading the SQL file to retrieve the database used.
So far, you are happy because it is an easy-to-build SQL query, so you start your dashboarding work from the following SQL query and display the result as KPIs in Data Studio :
