SQL
Comparing Consecutive Rows Using LAG and LEAD Window Functions
Analyze trends and compare values between previous and subsequent rows in a dataset with SQL's LAG and LEAD window functions, ideal for time-series analysis.
SELECT
sale_date,
sales_amount,
LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_sales,
sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS sales_difference_from_prev,
LEAD(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS next_day_sales
FROM
daily_sales
ORDER BY
sale_date;
How it works: This snippet utilizes the `LAG()` and `LEAD()` window functions to compare values in consecutive rows. `LAG(column, offset, default)` retrieves the value of `column` from a previous row, while `LEAD()` fetches it from a subsequent row, based on the specified `ORDER BY` within the `OVER` clause. This is incredibly useful for calculating differences, growth rates, or analyzing trends over time or other ordered sequences without self-joining.