SQL
Calculate Changes Between Consecutive Rows Using LAG
Learn to use SQL window functions like `LAG` or `LEAD` to compare a value from the current row with a value from a preceding or succeeding row.
SELECT
order_id,
order_date,
amount,
LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount,
amount - LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS amount_change
FROM orders
ORDER BY customer_id, order_date;
How it works: This snippet employs the `LAG` window function to retrieve the `amount` from the immediately preceding order for each `customer_id`, ordered by `order_date`. It then calculates the `amount_change` by subtracting the `previous_amount` from the current order's `amount`, effectively showing the transaction-to-transaction change for each customer.