The idea
Business questions sound different ("are we growing?", "what sells best?", "do new customers come back?"), but the queries behind them share one shape. First you decide the grain: the thing one row of your answer stands for, such as a month, a category or a signup quarter. Then you aggregate to that grain. Then you use a window to put each row next to the row you compare it with. Last, you decide what to divide by.
Most wrong numbers in a business review come from a mistake in one of those three decisions, not from a mistake in SQL syntax. The query runs, the chart looks plausible, and the number is still wrong.
Figure 1
The shape behind almost every business-review query
-
ordersone row per order -
monthly AS (… GROUP BY month)one row per month -
LAG(orders, 1) / LAG(orders, 12)look back, same rows -
SELECT … / growthone row per month, with a comparison
- Start from the raw rows. 12,000 orders are far too detailed for "are we growing?", and not yet in the shape a window needs.
- Aggregate first, in a CTE, to the grain you will report on: one row per month. Now each row is one comparable unit.
- Only now add a window.
LAG(orders, 1)reads the previous row (last month);LAG(orders, 12)reads twelve rows back (the same month last year). - The final
SELECTdivides this month by the looked-up one. Every decision that can go wrong lives in one of the first three boxes.
Why it exists
Counting orders is easy. Saying that revenue "is up" is hard, because it needs a baseline, and the baseline is a choice. Up compared to what: last month, the same month last year, the plan, the average? Whichever you pick has to be applied to the same kind of period, and defined the same way each time, or two people will report different numbers for the same quarter.
That is why this chapter practises patterns rather than functions. The functions you already know. What matters is the habit of building each metric in the same order and checking each step, so a review of 20 metrics does not need 20 new ideas.
How it works under the hood
Step 1: aggregate to the grain, then compare
Window functions work on rows. To compare months you need one row per month first, so the monthly
totals go in a CTE and the windows read from it. If you apply LAG to raw orders, you are comparing
an order with the order before it, which is not a month.
The grain also needs to be complete. LAG(orders, 12) means twelve rows back, which is the
same month last year only if no month is missing in between. Join the monthly totals to a calendar
so months with no orders show up as zero.
Step 2: choose the comparison
Month over month and year over year use the same window with a different offset.
Figure 2
The same month, two growth rates
| month | orders | prev_month | prev_year | mom | yoy |
|---|---|---|---|---|---|
| 2025-01 | 296 | 698 | 235 | -57.6% | +26.0% |
| 2025-02 | 235 | 296 | 189 | -20.6% | +24.3% |
| 2025-03 | 326 | 235 | 300 | +38.7% | +8.7% |
- January 2025 had 296 orders. Compared with the month before, December 2024 (698), that is a drop of 57.6%.
- Compared with January 2024 (235), the same month a year earlier, it is growth of 26.0%. December is the holiday peak, so "month over month" mostly measures the season.
- Both are correct. They answer different questions: MoM tells you what just changed, YoY tells you whether the business is growing once seasonality cancels out.
Growth is (this - previous) / previous, and the first period of each comparison has no previous
row, so it comes out as NULL. Divide with NULLIF(previous, 0) so a zero baseline gives NULL
instead of an error.
Two more rules. Compare whole periods with whole periods: a month that is only half over looks like a collapse next to a full one, which is the partial-month trap. And compare the same definition: if "orders" excludes cancelled ones this month, it has to exclude them last year too.
Step 3: reset when the question resets
A running total answers "how are we tracking?" only if it starts over when the period does.
Figure 3
A year-to-date total has to start over each January
| month | orders | ytd_reset | ytd_no_partition |
|---|---|---|---|
| 2024-11 | 629 | 3348 | 3348 |
| 2024-12 | 698 | 4046 | 4046 |
| 2025-01 | 296 | 296 | 4342 |
| 2025-02 | 235 | 531 | 4577 |
- Through December 2024 the running total reaches 4,046 orders, the whole year.
- Without a
PARTITION BYon the year, the window never stops. January 2025 shows 4,342: last year's orders plus this year's first month. SUM(orders) OVER (PARTITION BY year ORDER BY month)starts a fresh partition on January. Now January shows 296 and February 531, which is what "year to date" means.
Put the period in PARTITION BY (the year) and the sequence in ORDER BY (the month). The same
idea gives "customers so far this quarter" or "spend since signup".
Step 4: top N with ties, funnels and cohorts
These patterns follow the same recipe. Top N per group is a ranking window inside a CTE, and your
choice of ROW_NUMBER, RANK or DENSE_RANK is a statement about ties. A funnel counts how many
sessions reach each step, then divides each step by the one before: desktop sessions convert at
563 of 14,541, 3.9%, and mobile at 520 of 21,549, 2.4%, so mobile's problem is visible only when
you divide. A cohort grid groups customers by their first order and counts who ordered again in
each later period.
Step 5: state the denominator
Every percentage hides a denominator. "Activation rate" is activated customers divided by which customers: everyone who signed up, or only those whose quarter is over? In a retention grid, a cohort that signed up last month cannot have had twelve months to return, so it must be left out of the twelve-month column rather than counted as zero. Write the denominator in a sentence before you write the SQL.
Step 6: check one row by hand
Before you ship a pattern, pick one output row and rebuild it with a separate, simple query. Count January 2025's orders directly and compare it with the CTE. Look up January 2024 and divide by hand. If the hand number and the pattern agree on a row where something could have gone wrong (the first month of a year, a month with ties, the newest cohort), the rest is probably right. If they disagree, the grain, the window or the denominator is off, and now you know which row to debug.
Common misconceptions
"Growth is growth: I can compare any two periods"
A comparison measures the difference between two periods, so it inherits their differences. December against January compares a holiday peak with a quiet month. Use the same period a year earlier to remove the season, and use whole periods only.
"If the query runs and the total looks right, the pattern is right"
A grand total can be right while the monthly rows are wrong, for example when a running total carries over from one year to the next. Check a row you can verify by hand: the first month of a new year, the first cohort, the top product in one category.
"A LAG offset of 12 always means last year"
It means twelve rows back. With a missing month, or with several categories mixed without
PARTITION BY, twelve rows back can be a different month or a different category entirely. Build a
complete grid first and partition by whatever you compare within.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. The 57.6% compares January with December, the holiday peak. That mostly measures the season, not the health of the business.Try another answer.
Right. Against January 2024 (235 orders), January 2025 (296) is up 26.0%. Use year-over-year when the data is seasonal.
Not quite. MoM growth can be negative. The number is fine; the comparison is the weak part.Try another answer.
Not quite. January has only three fewer days than December. That explains a few percent at most, not a 57.6% drop.Try another answer.
Lesson done: it is ticked on the map.