SQL · medium

How would you build a month-over-month growth view in SQL for a dashboard?

Asked in BI Developer interviews, in the SQL round.

Short answer

Aggregate to month, then LAG the metric over ordered months: (this_month - LAG(metric) OVER (ORDER BY month)) / LAG(metric) OVER (ORDER BY month). Guard against divide-by-zero, and join to a month spine so a missing month reads as a gap rather than being silently skipped.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

orders

How to answer it

Definition

Aggregate the metric to a month grain, then compare each month with the one before it using LAG. The result is one row per month with the previous value, the absolute change and the percentage change.

Example

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM   orders
  GROUP BY 1
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM   monthly
ORDER BY month;

Output

month      | revenue | prev_revenue | change | pct_change
-----------+---------+--------------+--------+------------
2025-01-01 |  412000 |         NULL |   NULL |       NULL
2025-02-01 |  448500 |       412000 |  36500 |        8.9
2025-03-01 |  431200 |       448500 | -17300 |       -3.9

The three traps

• Division by zero. NULLIF(prev, 0) turns it into NULL rather than an error, and a percentage change from zero is undefined rather than infinite.
• Missing months. LAG returns the previous ROW, not the previous month. If March has no orders at all, April's "previous month" is February and the number is silently wrong. Join a month spine first.
• The current month is partial. Three days of April against all of March always shows a collapse. Exclude the open month or label it clearly.

Key points

• Aggregate to the month first, then window over the aggregate.
• NULLIF on the denominator, always.
• A month spine protects LAG from gaps, the same problem the rolling-window question has.
• The first row has no previous month. NULL is honest; zero is a lie.
• For seasonal businesses, year over year for the same month is often more meaningful, and saying so shows product sense.

FAQ

• LAG or a self join? LAG is clearer and cheaper. A self join on month minus one does the same and is easier to get subtly wrong.
• How do I add year over year? LAG(revenue, 12) OVER (ORDER BY month) on a complete monthly spine.
• Should the percentage be rounded in SQL? Round for display only, and keep full precision if anything downstream computes with it.

The 30-second answer

Aggregate to one row per month, then use LAG over the ordered months to bring the previous month alongside, and compute the difference and the percentage change from those two columns.

Three things I would guard. NULLIF on the denominator, because a month with zero revenue would otherwise divide by zero. A month spine, because LAG gives the previous row rather than the previous month, so a month with no data makes every later comparison wrong without raising an error. And the current month is incomplete, so I would exclude it or mark it, otherwise the dashboard shows a crash every first of the month.

In the interview

• What they are checking: LAG over a complete series, and the guard.
• Common mistake: no spine. The first month after a gap shows growth against the wrong baseline and nobody notices until the number is questioned.

Practice this for real

• Check your resume against a BI Developer job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card