Month-over-month revenue growth

For each month, show the revenue and the percentage change from the previous month, rounded to one decimal place.

What it tests

Aggregating to the right grain first, then LAG, then safe percentage maths — integer division and division by zero included.

The pattern

Period-over-period change. "Growth", "change from the previous", "month over month", "compared with last week".

How to approach it

  1. Aggregate orders to one row per month first (a CTE).
  2. Use LAG(revenue) OVER (ORDER BY month) to bring last month's revenue onto each row.
  3. Growth % = 100.0 × (this − last) ÷ last. Use 100.0 to avoid integer division and NULLIF(last, 0) to avoid dividing by zero.
  4. The first month has no previous month, so its growth is NULL.

Edge cases to mention

Common mistakes

Not true: Monthly growth: apply LAG to the single order rows, not to monthly totals.
In fact: On order rows, LAG compares one order with the order before it. Add up the revenue of each month first, then apply LAG to the monthly totals.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.