Chapter 11 · 12 min · Basic
Period over period without lying
"Is revenue up or down on last month?" is the most-asked question in analytics and the easiest one to answer wrongly with a query that runs clean.
The arithmetic is a subtraction. Everything that goes wrong happens before the subtraction: which rows counted as last month, whether last month exists in the result set at all, whether this month is finished, and whether the base you are dividing by means anything.
This chapter is four failures on one small table. Each one produces a number your stakeholder can read out loud, and none of them raises an error.
The dataset
Fourteen orders spanning November 2025 to April 2026. Three things are deliberately true of this table: November is a near-empty launch month, February has no orders at all, and April is still running — the last order is dated 9 April and today, for every query in this chapter, is 10 April 2026.
Schema
| order_id | int |
| order_date | date |
| amount_myr | numeric(8,2) |
Example data
The query everyone writes first
Aggregate to the month, LAG the previous value, subtract, divide. It is four lines and it is what almost every month-over-month report on earth is built on.
Run it and read the March row. It says revenue grew 16.7% month over month.
There was no February. LAG does not walk months — it walks the rows the query returned, and February returned no row, so "the previous month" for March is January. The number is a real comparison of two real months; it is just not the comparison the column header claims.
Read the April row too: down 65.7%. Hold that one; it is a different lie and it gets its own section.
And the December row reads +2400%, which is arithmetically perfect and completely useless.
Six months of orders are seeded. How many rows will this return, and what does the missing one do to the row after it?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Build the calendar, then join the data to it
- 03A percentage needs a base worth dividing by
- 04The month that is not over
- 05Compare the same slice of each period
- 06Now do it where the calendar is somebody else's problem