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.

How to answer it

Aggregate to the month, then compare each month with the one before it using LAG.

WITH months AS (
  SELECT generate_series(DATE '2025-01-01', DATE '2026-08-01', INTERVAL '1 month')::date AS month
),
monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
series AS (
  SELECT m.month, COALESCE(r.revenue, 0) AS revenue
  FROM months m
  LEFT JOIN monthly r ON r.month = m.month
),
with_prev AS (
  SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM series
)
SELECT
  month,
  revenue,
  prev_revenue,
  CASE
    WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
    ELSE (revenue - prev_revenue) / prev_revenue
  END AS mom_growth
FROM with_prev
ORDER BY month;

Three details make it dashboard-safe. The month spine, so a month with no orders appears as 0 instead of being skipped, which would make LAG compare against the wrong month. The divide-by-zero guard, which returns NULL rather than an error or infinity (and note that x IN (0, NULL) would not catch the NULL; it has to be IS NULL). And the DATE_TRUNC, so the grouping key is a real date the chart can sort, not a formatted string.

• 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

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