Month-over-month change with LAG
Compare each month with the month before it using LAG.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
SUM(amount) AS amount
FROM orders
GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
amount,
LAG(amount) OVER (ORDER BY month) AS previous_amount,
ROUND(100.0 * (amount - LAG(amount) OVER (ORDER BY month))
/ LAG(amount) OVER (ORDER BY month), 1) AS change_pct
FROM monthly
ORDER BY month;How it works
LAG(amount) returns the value of the previous row in the order given. For the first month there is no previous row, so the result is NULL, and the percentage is NULL as well.
The percentage is (this month - last month) / last month. Multiplying by 100.0 (not 100) avoids integer division in databases that use it.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
month | amount | previous_amount | change_pct
2024-01 | 300.0 | NULL | NULL
2024-02 | 450.0 | 300.0 | 50.0
2024-03 | 620.0 | 450.0 | 37.8Common mistakes and limitations
- Dividing by a previous value of zero. Wrap the division in NULLIF(LAG(...), 0). - Ordering by a text month name instead of a sortable value such as 2024-03. - Missing months (no orders) are not in the data, so LAG compares with the last month that exists, not the calendar month before.