Running Totals, LAG and Moving Averages

Rania’s monthly business review needs three things on one slide: revenue by month, how much of the year’s total has accumulated so far, and whether each month grew on the last. You could compute the year-to-date figure in a spreadsheet afterwards. But the same window functions that ranked products last lesson can also accumulate and compare, so the whole table comes straight out of SQL, reproducibly.

Running totals and year-to-date

A window aggregate with an ORDER BY inside OVER becomes a running calculation: each row sees the rows up to itself. Add PARTITION BY and it restarts per group. Here are Cartwheel’s monthly orders with a year-to-date count that resets each January:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT
  month,
  orders,
  SUM(orders) OVER (
    PARTITION BY substr(month, 1, 4)
    ORDER BY month
  ) AS orders_ytd
FROM monthly
ORDER BY month
LIMIT 14;

The partition key is the year, taken from the first four characters of the month label. December 2024 closes the year at 552 orders, and January 2025 starts again at 84.

Two windows on the same row combine nicely. Dividing the running total by the total of the whole partition, 100.0 * SUM(orders) OVER (PARTITION BY substr(month, 1, 4) ORDER BY month) / SUM(orders) OVER (PARTITION BY substr(month, 1, 4)), gives the cumulative share of the year reached by each month. For a seasonal business like Cartwheel, that’s the number finance actually tracks: by the end of October 2025, the year was just under two-thirds done.

Comparing with the previous row

LAG(x) returns the value of x from the previous row in the window order, and LEAD(x) from the next. Period-over-period change is then simple arithmetic:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT
  month,
  orders,
  LAG(orders) OVER (ORDER BY month) AS prev_month,
  orders - LAG(orders) OVER (ORDER BY month) AS change,
  ROUND(100.0 * (orders - LAG(orders) OVER (ORDER BY month))
        / NULLIF(LAG(orders) OVER (ORDER BY month), 0), 1) AS change_pct,
  LAG(orders, 12) OVER (ORDER BY month) AS same_month_last_year
FROM monthly
ORDER BY month DESC
LIMIT 4;

November 2025 jumped by 193 orders, then December fell by 140: Black Friday, then the hangover. Month-over-month numbers are noisy for a seasonal business, which is why the last column uses LAG(orders, 12), the value twelve rows back, for a year-over-year view: 325 orders this November against 120 last November.

LAG(orders, 12) is only “the same month last year” if every month has a row. A month with no orders would have no row, and every comparison after it would silently shift by one month. That assumption is about to matter.

Moving averages and the frame

A moving average smooths daily noise. The window needs an explicit frame, the range of rows around the current one that the aggregate sees: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the current row plus the six before it.

The frame counts rows, not days. If a day has no orders, it has no row, and the frame quietly stretches further back in time. Cartwheel’s first month shows how badly that can mislead: in January 2024 only 8 orders arrived, on 8 different days. A 7-row average over the existing rows says “1.0 orders a day” all month. The truth is about a quarter of that.

The fix is a calendar: generate one row per day with a recursive CTE, LEFT JOIN the daily counts, and turn missing days into zeros:

WITH RECURSIVE days(day) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2024-01-31'
),
daily AS (
  SELECT date(order_date) AS day, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
    AND order_date >= '2024-01-01' AND order_date < '2024-02-01'
  GROUP BY day
),
filled AS (
  SELECT d.day, COALESCE(x.orders, 0) AS orders
  FROM days AS d
  LEFT JOIN daily AS x ON x.day = d.day
),
smoothed AS (
  SELECT day, orders,
         ROUND(AVG(orders) OVER (
           ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
         ), 2) AS orders_7d_avg
  FROM filled
)
SELECT * FROM smoothed
WHERE day >= '2024-01-25'
ORDER BY day;

Now 31 January shows 0.43 orders a day over the last week, the honest number. Notice where the date filter sits: in the final SELECT, after the moving average is computed in smoothed. Window functions run after WHERE, so filtering in the same query would leave the first displayed days with no history to average. The same calendar trick protects LAG(orders, 12) from missing months.

You can now rank, accumulate and compare. The next lesson puts these tools to work on the two analyses every product team asks for: funnels and cohort retention.