SQL LAG() Function Example
Let’s look at some examples of SQL LAG function and understand how to use LAG Function in SQL Server.
Example 1
SELECT Organisation, [Year], Revenue,
LAG (Revenue, 1, 0)
OVER (PARTITION BY Organisation ORDER BY [Year]) AS PrevYearRevenue
FROM Org
ORDER BY Organisation, [Year];
Output:
Organisation | Year | Revenue | PrevYearRevenue |
---|---|---|---|
ABCD News | 2013 | 440000 | 0 |
ABCD News | 2014 | 480000 | 440000 |
ABCD News | 2015 | 490000 | 480000 |
ABCD News | 2016 | 500000 | 490000 |
ABCD News | 2017 | 520000 | 500000 |
ABCD News | 2018 | 525000 | 520000 |
ABCD News | 2019 | 540000 | 525000 |
ABCD News | 2020 | 550000 | 540000 |
Z News | 2016 | 720000 | 0 |
Z News | 2017 | 750000 | 720000 |
Z News | 2018 | 780000 | 750000 |
Z News | 2019 | 880000 | 780000 |
Z News | 2020 | 910000 | 880000 |
In the above example, We have 2 TV News Channel whose Current and Previous Year’s Revenue is presented on the same row using the LAG() function. As You can see that the very first record for each of the TV News channels don’t have previous year revenues so it shows the default value of 0. This function can be very useful in yielding data for BI reports when you want to compare values in consecutive periods, for e.g. Year on Year or Quarter on Quarter or Daily Comparisons.
Example 2
SELECT Z.*, (Z.Revenue - z.PrevYearRevenue) as YearonYearGrowth
FROM (SELECT Organisation, [Year], Revenue,
LAG (Revenue, 1)
OVER (PARTITION BY Organisation ORDER BY [Year] ) AS PrevYearRevenue
FROM Org) Z ORDER BY Organisation, [Year];
Output:
Organisation | Year | Revenue | PrevYearRevenue | YearOnYearGrowth |
---|---|---|---|---|
ABCD News | 2013 | 440000 | NULL | NULL |
ABCD News | 2014 | 480000 | 440000 | 40000 |
ABCD News | 2015 | 490000 | 480000 | 10000 |
ABCD News | 2016 | 500000 | 490000 | 10000 |
ABCD News | 2017 | 520000 | 500000 | 20000 |
ABCD News | 2018 | 525000 | 520000 | 5000 |
ABCD News | 2019 | 540000 | 525000 | 15000 |
ABCD News | 2020 | 550000 | 540000 | 10000 |
Z News | 2016 | 720000 | NULL | NULL |
Z News | 2017 | 750000 | 720000 | 30000 |
Z News | 2018 | 780000 | 750000 | 30000 |
Z News | 2019 | 880000 | 780000 | 100000 |
Z News | 2020 | 910000 | 880000 | 30000 |
In the above example, We can similarly calculate Year On Year Growth for the TV News Channel. Also, one thing to notice in this example is we haven’t supplied any default parameter to LAG(), and hence the LAG() function returns NULL in case there are no previous values. The LAG() function can be implemented at the database level and BI Reporting solutions like Power BI and Tableau can avoid using the cumbersome measures at the reporting layer.
SQL LAG() Function
The SQL LAG() function is a window function that provides access to a row at a specified physical offset which comes before the current row.
LAG function in SQL Server is used to compare current row values with values from the previous row.
Contact Us