SQL LAG Function Explained

The LAG() function is a window function in SQL that allows you to access data from a previous row in the same result set without using a self-join. It is commonly used to compare the current row’s value with the value of a previous row.

Syntax

LAG(return_value , offset [, default])
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• return_value: The value to be returned from the previous row.
• offset: The number of rows back from the current row from which to obtain a value. The default is 1.
• default: The value to return if the offset goes beyond the scope of the partition. The default is NULL.
• PARTITION BY: Divides the result set into partitions to which the LAG() function is applied.
• ORDER BY: Specifies the order of the rows in each partition.

Example 1: Comparing Current Year Sales with Previous Year

Imagine we have a sales table with annual sales data.

We want to retrieve the previous year’s sales for comparison.

SELECT 
    Year,
    TotalSales,
    LAG(TotalSales, 1, 0) OVER (ORDER BY Year) AS PreviousYearSales
FROM sales;

Result:

Example 2: Calculating Month-over-Month Growth

You can use LAG() to calculate the difference between the current row and the previous row.

SELECT 
    Month,
    Revenue,
    Revenue - LAG(Revenue) OVER (ORDER BY Month) AS RevenueData
FROM monthly_revenue;

Key Takeaways

• LAG() is perfect for calculating deltas, growth rates, and time-series comparisons.
• Always ensure your ORDER BY clause inside the OVER() partition is correct, as it determines which row is considered “previous”.
• Use the default parameter to handle the first row gracefully (e.g., returning 0 instead of NULL).