Time-series analysis
Harry pulls up NOVA’s monthly revenue for a leadership update and stares at it. It’s all over the place. “Is this trend real, or just noise?”
Familiar shape from the revenue lesson — completed orders, grouped by month:
SELECT SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month, ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month;Running…
August looks great — almost $300. Then September craters to $39. Is NOVA in trouble?
Before you panic — how many completed orders were in each of those months?
August had four completed orders. September had one. A single month-to-month comparison, when the underlying counts are that small, is closer to a coin flip than a trend line. One canceled checkout or one delayed order can swing the whole month. Time-series analysis is exactly the discipline of not reacting to that — smoothing the series so a real, sustained move stands out from ordinary month-to-month noise.
One month is a data point. Several months, smoothed, is a trend.
You’ve already built this exact window — a trailing moving average, same frame clause as the moving-averages lesson — plus LAG for the raw month-over-month change, so you can see the smoothed and the raw number side by side:
WITH monthly AS (
SELECT SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY month
)
SELECT
month,
ROUND(revenue, 2) AS revenue,
ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change_from_prior_month,
ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS trailing_3mo_avg
FROM monthly
ORDER BY month;Running…
Wait. Count the rows — there's only 9 of them. There should be 10 months between December 2022 and September 2023.
Good catch. Which one's missing?
June 2023 is missing. Not zero — absent. Check why:
SELECT status, COUNT(*), SUM(amount)
FROM orders
WHERE SUBSTR(CAST(placed_at AS TEXT), 1, 7) = '2023-06'
GROUP BY status;NOVA had two orders in June — one cancelled, one refunded. Zero completed. GROUP BY only ever produces a row for a group that exists in the filtered data, so a month with zero completed orders doesn’t appear as a 0.00 row — it just doesn’t appear.
That’s more than a cosmetic gap. Look again at the moving average table above: the ROWS BETWEEN 2 PRECEDING AND CURRENT ROW frame counts result rows, not calendar months. The “3-month average” on the July row is silently averaging April, May, and July — June was never a row to begin with, so the window slides right past it without knowing anything was skipped.
A ROWS-based window trusts that every row is one period. If a period can go missing from your data, that trust is misplaced.
Try it: Does the same gap show up if you don't filter to status = 'completed'?
Run your query to see results here.
No — ten rows, every month accounted for, because June had orders, just none that completed. The gap is entirely a side effect of the WHERE status = 'completed' filter combined with GROUP BY. This is exactly the kind of thing worth checking whenever a time series looks thinner than you expect: is a period actually zero, or did it just not survive your filter?
Finance wants a specific answer: which single month had the largest month-over-month drop in completed revenue? Show the month, its revenue, and the change from the prior month.
Hint 1
Build the monthly revenue CTE, then compute change_from_prior_month with LAG, same as the worked example above.
Hint 2
You want the most negative change — order by it ascending, not descending.
Hint 3
ORDER BY change_from_prior_month ASC LIMIT 1 gets you the single biggest drop.
Solution
Running…
September 2023 is the real answer — and notice the drop is measured against August, not against the missing June, because LAG only ever sees the rows that exist. Two lessons in one query: small-sample months look like crises that aren’t, and a filtered GROUP BY can silently delete a period instead of reporting it as zero. Both are “is this trend real” traps, and both are checkable — you just have to think to check.
Before trusting a trend: check the sample size behind each point, and check that every period you expect actually produced a row — ROWS-based window frames have no idea a period went missing, they just keep counting rows.