Write a query that shows this month's revenue next to last month's, so I can see the change.
You work out the revenue for each month first, and then line the months up against each other.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
order_date TEXT NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (id, order_date, amount) VALUES
(1, '2026-01-14', 250.00),
(2, '2026-01-28', 120.00),
(3, '2026-02-03', 900.00),
(4, '2026-02-19', 45.00);
-- The revenue for each month.
WITH monthly AS (
SELECT
SUBSTR(order_date, 1, 7) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY month
)
SELECT
month,
revenue,
-- The same month one row earlier.
LAG(revenue) OVER (ORDER BY month) AS previous_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;
month revenue previous_month change
2026-01 370.00 (missing) (missing)
2026-02 945.00 370.00 575.00
So what is happening there? The first part works out the revenue for each month, and it gives one row per month. The window function LAG then looks at the row before the current one in that answer, so the second row can see the first month's number.
There is one detail to know about the window function. It looks at the rows that are already in the answer, so the first month has no row before it, and the two values for it are missing rather than zero.
The next question in this topic asks about a slow query, which is the more useful half of this question.