Month-over-month revenue growth

Step 1 Solve it yourself first

Tables you will use

orders

What it really 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".

Picture it

flowchart LR
  O["orders"] --> M["monthly revenue<br/>Jan 445 · Feb 210 · Mar 138"]
  M --> L["LAG brings the month before<br/>Feb sees 445 · Mar sees 210"]
  L --> G["growth %<br/>Feb −52.8 · Mar −34.3"]

The approach, step by step

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.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER w)
             / NULLIF(LAG(revenue) OVER w, 0), 1) AS growth_pct
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  Mode SQL Tutorial — SQL Window Functions: LAG and LEAD

  Mode, free

• Practice

  DataLemur — month-over-month growth questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why you aggregate to months before using LAG.

   Show answer

   LAG returns the previous row, so the rows must be the things you want to compare: months. On the raw orders table, the previous row is the previous order, so "growth" would compare one order with another order.

   So first collapse to one row per month (GROUP BY month, SUM(amount)) in a CTE, and only then use LAG over those monthly rows. The previous row is now the previous month.

   (The order of the steps matters for a second reason too: window functions run after GROUP BY, so even in one query, LAG over a SUM is computed on the grouped rows.)

2. Writefrom a blank page, then run it

   Compute month-over-month growth in the number of orders, not revenue.

   Show answer

   Swap SUM(amount) for COUNT(*). January has 4 orders, February 3 (−25%) and March 3 (0%).

   One question to ask: do cancelled orders count? Order 106 is in February's 3. Excluding it would make February 2 and March +50%.

   sqlCopy

   WITH monthly AS (
     SELECT strftime('%Y-%m', order_date) AS month,
            COUNT(*) AS orders
     FROM orders
     GROUP BY strftime('%Y-%m', order_date)
   )
   SELECT month, orders,
          ROUND(100.0 * (orders - LAG(orders) OVER (ORDER BY month))
                / LAG(orders) OVER (ORDER BY month), 1) AS growth_pct
   FROM monthly
   ORDER BY month;

   Try it

   3 rows

   month	orders	growth_pct
   2024-01	4	NULL
   2024-02	3	-25
   2024-03	3	0

3. Predictsay it before you run it

   What would growth_pct be for March if March revenue were 315?

   Show answer

   +50.0. February's revenue is 210, so (315 − 210) ÷ 210 = 105 ÷ 210 = 0.5, which is 50%.

   The query below makes March 315 by adding 177 to order 110 (138 + 177 = 315).

   sqlCopy

   UPDATE orders SET amount = amount + 177 WHERE order_id = 110;
   WITH monthly AS (
     SELECT strftime('%Y-%m', order_date) AS month,
            SUM(amount) AS revenue
     FROM orders
     GROUP BY strftime('%Y-%m', order_date)
   )
   SELECT month, revenue,
          ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
                / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
   FROM monthly
   ORDER BY month;

   Try it

   3 rows

   month	revenue	growth_pct
   2024-01	445	NULL
   2024-02	210	-52.8
   2024-03	315	50

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Show what goes wrong if you remove the 100.0 and use whole-number maths, then fix it.

   Show answer

   Revenue is a whole number, so with whole-number maths the division throws away the fraction. (210 − 445) ÷ 445 is −0.53, which becomes 0, and 0 × 100 = 0: February shows 0% growth instead of −52.8%. March does the same. Nothing errors; the numbers are just wrong.

   The fix is to make one side a decimal before dividing: multiply by 100.0 first, as the solution does. Multiplying by 100 (whole) first would be closer but still cut the decimals off: −52 instead of −52.8.

   sqlCopy

   WITH monthly AS (
     SELECT strftime('%Y-%m', order_date) AS month,
            SUM(amount) AS revenue
     FROM orders
     GROUP BY strftime('%Y-%m', order_date)
   )
   SELECT month, revenue,
          (revenue - LAG(revenue) OVER (ORDER BY month))
            / LAG(revenue) OVER (ORDER BY month) * 100 AS whole_number_maths,
          ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
                / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
   FROM monthly
   ORDER BY month;

   Try it

   3 rows

   month	revenue	whole_number_maths	growth_pct
   2024-01	445	NULL	NULL
   2024-02	210	0	-52.8
   2024-03	138	0	-34.3

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I aggregate to one row per month in a CTE, then use LAG over month order to bring the previous month's revenue alongside, and compute 100.0 times the difference divided by the previous value, rounded. I guard the division with NULLIF for zero months, note that the first month is NULL, and would fill in missing months with a calendar join if gaps are possible.

Follow-up questions

Tick each check when you can do it without notes