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
- Aggregate orders to one row per month first (a CTE).
- Use
LAG(revenue) OVER (ORDER BY month)to bring last month's revenue onto each row. - Growth % = 100.0 × (this − last) ÷ last. Use 100.0 to avoid integer division and
NULLIF(last, 0)to avoid dividing by zero. - The first month has no previous month, so its growth is NULL.
Edge cases to mention
- The first month: no previous month, so NULL.
- A previous month with 0 revenue: divide by
NULLIF(…, 0). - A month with no orders at all has no row, so LAG would compare with two months ago — build a month list and LEFT JOIN if that matters.
Common mistakes
- Using LAG on the order rows instead of the monthly totals.
(revenue - prev) / prev * 100with whole numbers: integer division gives 0 or -1.- Grouping by order_date instead of the month.
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.