Level 2 · Month-over-month vs year-over-year

“Are we growing?” has two common answers, and in a seasonal business they often disagree. Month-over-month (MoM) compares a month with the one before it. Year-over-year (YoY) compares it with the same month a year earlier. Good reports show both and know which one to trust.

LAG with an offset

LAG(x) looks one row back. LAG(x, n) looks n rows back. On a monthly table with one row per month and no gaps, 12 rows back is the same month last year:

LAG(net_revenue, 1)  OVER (ORDER BY month) AS prev_month,
LAG(net_revenue, 12) OVER (ORDER BY month) AS same_month_last_year

Growth is then (current - previous) / previous * 100. Write 100.0 so the math stays decimal on every database.

Worked example: the January “collapse”

Run on completed revenue, three 2025 months tell the story:

month	net_revenue	mom_growth_pct	yoy_growth_pct
2025-01-01	150,570.98	-48.2	37.8
2025-09-01	143,826.50	-21.6	-5.3
2025-11-01	292,364.40	67.4	-8.4

January “fell by half” only because December is the holiday peak. Against January 2024 it grew 37.8%. November looks like a triumph on MoM (+67.4%, Black Friday), yet it was 8.4% below November 2024. The YoY column is the one that removes seasonality: it compares like with like.

The safer YoY: join on the date

LAG(x, 12) silently breaks when a month is missing, because “12 rows back” is no longer “12 months back”. A self-join on the date never drifts:

SELECT cur.month, cur.net_revenue, prev.net_revenue AS last_year
FROM monthly AS cur
LEFT JOIN monthly AS prev
  ON prev.month = cur.month - INTERVAL 1 YEAR;

At Harbor & Pine every month has sales, so both forms give the same numbers. On sparse data (a new product, a small region) prefer the join, or build a full calendar first.

Filter after the window

Compute LAG over all months and keep 2025 in the outer query. A WHERE on 2025 inside the CTE runs before the window, so January has no December and no month has a previous year.

Common mistakes

• Reporting MoM as the headline in a seasonal business. It mostly measures the calendar.
• Dividing by the wrong base. Growth divides by the earlier value, not the current one.
• A partial last month. If the current month is not over, both comparisons understate it. The quiz later in this chapter is about exactly that.

In SQL Server

LAG(net_revenue, 12) OVER (ORDER BY month) is identical. For the join form, use DATEADD(year, -1, cur.month). Watch integer columns: 100 * (a - b) / b truncates when all values are integers, so keep the 100.0.

LEFT JOIN monthly AS prev ON prev.month = DATEADD(year, -1, cur.month)