Example 1
Calculate the day-over-day revenue change for each date in a sales log
The first row has no preceding row so prev_revenue and revenue_change are NULL. Each subsequent row subtracts the previous day's revenue from the current day's. Jan 3 shows a drop of 30 (120 − 150 = −30). No PARTITION BY is used because all rows belong to a single series.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE sales (id INT, sale_date VARCHAR(10), revenue INT);
INSERT INTO
sales (id, sale_date, revenue)
VALUES
(1, '2024-01-01', 100),
(2, '2024-01-02', 150),
(3, '2024-01-03', 120),
(4, '2024-01-04', 200);Validated query Shared across supported engines.
SQL
SELECT
sale_date,
revenue,
LAG (revenue) OVER (
ORDER BY
sale_date
) AS prev_revenue,
revenue - LAG (revenue) OVER (
ORDER BY
sale_date
) AS revenue_change
FROM
sales
ORDER BY
sale_date;Expected result Returned rows for the shared example.
| sale_date | revenue | prev_revenue | revenue_change |
|---|---|---|---|
| 2024-01-01 | 100 | NULL | NULL |
| 2024-01-02 | 150 | 100 | 50 |
| 2024-01-03 | 120 | 150 | -30 |
| 2024-01-04 | 200 | 120 | 80 |
Output is identical across all supported engine versions.