The LAG() Function

The LAG() function in SQL is used to access data from a previous row in the same result set without the need for a self-join. This function is often used in time series analysis or to calculate differences between consecutive rows.

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

Parameters:

• column_name: The column from which to retrieve the lag value.
• offset: The number of rows back from the current row from which to obtain a value. The default is 1.
• default_value: The value to return if the offset goes out of the bounds of the partition. If omitted, the default is NULL.
• PARTITION BY partition_column: (Optional) Divides the result set into partitions to which the LAG() function is applied. If omitted, the entire result set is treated as a single partition.
• ORDER BY order_column: Specifies the order of the rows within each partition.

We can use the LAG() function to calculate the difference in sales amount from the previous day:

SELECT
    sale_id,
    sale_date,
    sale_amount,
    sale_amount - LAG(sale_amount, 1, 0) OVER (ORDER BY sale_date) AS difference_from_previous_day
FROM
    sales;