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
monthwebsiteleedsharrogate
2024-011990115019NULL
2024-022059015877NULL
2024-032706419917NULL
2024-042974423192NULL
2024-053277224841NULL
2024-063580127106NULL
2024-073727230058NULL
2024-083460426490NULL
2024-092778219916NULL
2024-102535219857NULL
2024-113149924481NULL
2024-123684926532NULL
2025-012270916480NULL
2025-022244718178NULL
2025-032868222662NULL
2025-04316872460713821
2025-05382442937823710
2025-06373813067724189
2025-07444173541527917
2025-08409333060326075
2025-09303992452519584
2025-10308072493720314
2025-11381962690722521
2025-12409302863323622
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;
The chain's revenue, month on 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.7

The 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:

Harrogate's first two months
monthtrading_daysrevenuechange_pct
2025-041913821NULL
2025-05312371071.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:

2025 against 2024
storesrevenue_2024revenue_2025growth_pct
HarrogateNULL201753NULL
Website and Leeds63251671983413.8
All stores63251692158745.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…