Compare Current and Previous Rows with LAG/LEAD Window Functions

SELECT
    transaction_id,
    transaction_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS amount_change
FROM
    Transactions
ORDER BY
    customer_id, transaction_date;

How it works: This query uses the `LAG()` window function to retrieve the `amount` from the immediately preceding row for each `customer_id`, ordered by `transaction_date`. The `1` indicates the offset (1 row back), and `0` is the default value if no preceding row exists. It then calculates the difference (`amount_change`) between the current transaction's amount and the previous one, useful for tracking changes over time per customer.