Interview › Month over month

SQL interview: month-over-month growth

Two grains: first the month, then the previous month. Do not LAG the raw orders.

Prompt. Sales by month, previous month, and percent change.

Hint: LAG(sales) OVER (ORDER BY ym)

Your T-SQL

WITH m AS (
  SELECT CONVERT(char(7), order_date, 120) AS ym,
         SUM(total_amount) AS sales
  FROM Orders
  GROUP BY CONVERT(char(7), order_date, 120)
)
SELECT ym, sales,
       LAG(sales) OVER (ORDER BY ym) AS prev_sales,
       (sales - LAG(sales) OVER (ORDER BY ym)) * 1.0
         / NULLIF(LAG(sales) OVER (ORDER BY ym), 0) AS mom
FROM m;

First month’s LAG is NULL. NULLIF stops a divide by zero.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free