SQL for Analysis · 16 min · 190 XP
LAG and period-over-period change
Compare each period with the one before, and know when the comparison is fair.
"Up 12% on last month" needs two numbers on the same row: this month and last. LAG(x) OVER (ORDER BY ...) fetches x from the previous row, without a self-join. LEAD fetches the next one. LAG(x, 12) reaches back 12 rows. A third argument, as in LAG(x, 1, 0), is the value to use when there's no row that far back. Keep it for a count that really was 0 then, never as a stand-in for a period nobody measured: the next section explains why.
Revenue per store, month by month24 rows
| month | website | leeds | harrogate |
|---|---|---|---|
| 2024-01 | 19901 | 15019 | NULL |
| 2024-02 | 20590 | 15877 | NULL |
| 2024-03 | 27064 | 19917 | NULL |
| 2024-04 | 29744 | 23192 | NULL |
| 2024-05 | 32772 | 24841 | NULL |
| 2024-06 | 35801 | 27106 | NULL |
| 2024-07 | 37272 | 30058 | NULL |
| 2024-08 | 34604 | 26490 | NULL |
| 2024-09 | 27782 | 19916 | NULL |
| 2024-10 | 25352 | 19857 | NULL |
| 2024-11 | 31499 | 24481 | NULL |
| 2024-12 | 36849 | 26532 | NULL |
| 2025-01 | 22709 | 16480 | NULL |
| 2025-02 | 22447 | 18178 | NULL |
| 2025-03 | 28682 | 22662 | NULL |
| 2025-04 | 31687 | 24607 | 13821 |
| 2025-05 | 38244 | 29378 | 23710 |
| 2025-06 | 37381 | 30677 | 24189 |
| 2025-07 | 44417 | 35415 | 27917 |
| 2025-08 | 40933 | 30603 | 26075 |
| 2025-09 | 30399 | 24525 | 19584 |
| 2025-10 | 30807 | 24937 | 20314 |
| 2025-11 | 38196 | 26907 | 22521 |
| 2025-12 | 40930 | 28633 | 23622 |
The query
SELECT strftime('%Y-%m', day) AS month,
SUM(CASE WHEN store_id = 1 THEN revenue END) AS website,
SUM(CASE WHEN store_id = 2 THEN revenue END) AS leeds,
SUM(CASE WHEN store_id = 3 THEN revenue END) AS harrogate
FROM daily_sales
GROUP BY month
ORDER BY month;WITH monthly AS (
SELECT strftime('%Y-%m', day) AS month,
SUM(revenue) AS revenue
FROM daily_sales
GROUP BY month
), compared AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly
)
SELECT month, revenue,
revenue - prev AS change,
ROUND(100.0 * (revenue - prev) / prev, 1) AS pct
FROM compared
ORDER BY month
LIMIT 4;
-- month revenue change pct
-- 2024-01 34920.0 NULL NULL
-- 2024-02 36467.0 1547.0 4.4
-- 2024-03 46981.0 10514.0 28.8
-- 2024-04 52936.0 5955.0 12.7The first row has nothing before it
January 2024 has nothing before it, so its change is NULL, and NULL is the honest answer. Don't COALESCE it to 0: a 0 claims the month was flat, when in fact nobody knows.
Watch out for two arithmetic traps. Write 100.0 *, not 100 *: orders is an INTEGER, and dividing one integer by another gives an integer, so every change under 100% comes out as 0. And if the previous value can be 0, divide by NULLIF(prev, 0). SQLite returns NULL when you divide by zero, but PostgreSQL and most other databases stop with an error.
The previous row isn't always the previous period
LAG means the previous row, not the previous period. On Leeds's daily rows, Monday's previous row is Saturday, because Sunday has no row. Monthly totals here have every month, so LAG(revenue, 12) is the same month last year. Where a period might be missing, build a calendar first, as in the last lesson, and then LAG over that.
PARTITION BY store_id compares each store only with itself. Watch the first row of a new store, though. Harrogate opened on 12 April 2025, part-way through the month:
| month | trading_days | revenue | change_pct |
|---|---|---|---|
| 2025-04 | 19 | 13821 | NULL |
| 2025-05 | 31 | 23710 | 71.6 |
The query
WITH monthly AS (
SELECT strftime('%Y-%m', day) AS month,
COUNT(*) AS trading_days,
SUM(revenue) AS revenue
FROM daily_sales
WHERE store_id = 3
GROUP BY month
)
SELECT month, trading_days, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS change_pct
FROM monthly
ORDER BY month
LIMIT 2;May is up 71.6% for no reason except that it's a whole month. A partial first period makes the second look like a boom, so compare from the first full one.
Like for like: better, or just bigger?
Then there's the question of whether the comparison is fair at all. Harrogate didn't exist in 2024, so everything it took in 2025 looks like growth:
| stores | revenue_2024 | revenue_2025 | growth_pct |
|---|---|---|---|
| Harrogate | NULL | 201753 | NULL |
| Website and Leeds | 632516 | 719834 | 13.8 |
| All stores | 632516 | 921587 | 45.7 |
The query
WITH yearly AS (
SELECT CASE WHEN s.opened_on <= '2024-01-01'
THEN 'Website and Leeds' ELSE 'Harrogate' END AS stores,
SUM(CASE WHEN d.day < '2025-01-01' THEN d.revenue END) AS y2024,
SUM(CASE WHEN d.day >= '2025-01-01' THEN d.revenue END) AS y2025
FROM daily_sales AS d
JOIN stores AS s ON s.id = d.store_id
GROUP BY 1
)
SELECT stores, y2024 AS revenue_2024, y2025 AS revenue_2025,
ROUND(100.0 * (y2025 - y2024) / y2024, 1) AS growth_pct
FROM yearly
UNION ALL
SELECT 'All stores', SUM(y2024), SUM(y2025),
ROUND(100.0 * (SUM(y2025) - SUM(y2024)) / SUM(y2024), 1)
FROM yearly
ORDER BY revenue_2025;Both growth figures are true. The 13.8% for the stores that traded in both years is called like-for-like growth, and it's the one that tells you whether the business is doing better, rather than just bigger.
Two checks before you publish a period comparison. Is every period there, so that the previous row is really the previous period? And is the same set of things being counted in both periods?
Loading your workspace…