Month-over-Month and Year-over-Year Growth

Difficulty: Easy · Topics: lag, window-functions, time-series · Asked at: Google, Netflix, Airbnb

Problem

From daily orders, compute monthly revenue and:

• prev_month_revenue and mom_pct (percentage change vs the previous calendar month, rounded to 1 decimal)
• yoy_pct: percentage change vs the same month one year earlier (NULL if not available)

Months with no orders don’t exist in this data. Order by month.

Schema and sample data

CREATE TABLE orders (order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
('2025-01-15',100),('2025-02-10',120),('2025-03-03',90),
('2026-01-05',150),('2026-01-20',50),('2026-02-14',210),('2026-03-30',180);

Expected output

Hints

Solution

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT m.month, m.revenue,
       LAG(m.revenue) OVER (ORDER BY m.month) AS prev_month_revenue,
       ROUND(100.0 * (m.revenue - LAG(m.revenue) OVER (ORDER BY m.month))
             / LAG(m.revenue) OVER (ORDER BY m.month), 1) AS mom_pct,
       ROUND(100.0 * (m.revenue - ly.revenue) / ly.revenue, 1) AS yoy_pct
FROM monthly m
LEFT JOIN monthly ly
  ON ly.month = strftime('%Y-%m', DATE(m.month || '-01', '-1 year'))
ORDER BY m.month;

Explanation

• LAG compares with the previous row, which equals the previous calendar month only if no months are missing. Here 2025-03 → 2026-01 is a 10-month jump, so the “MoM” for 2026-01 compares against March 2025. Point this out in an interview, then fix it by densifying with a calendar of months or by joining on month - 1.
• For YoY the solution joins on the computed month string, which is robust to gaps. LAG(revenue, 12) would be wrong here.

Follow-up questions