Interview › Year over year

SQL interview: year-over-year with LAG

YoY is MoM with a different lag. Get the grain right first.

Prompt. Annual sales and the prior year’s sales.

Hint: LAG(sales, 1) OVER (ORDER BY yr)

Your T-SQL

WITH y AS (
  SELECT DATEPART(year, order_date) AS yr, SUM(total_amount) AS sales
  FROM Orders
  GROUP BY DATEPART(year, order_date)
)
SELECT yr, sales, LAG(sales) OVER (ORDER BY yr) AS prev_year
FROM y;

Same month last year: grain to year-month, LAG(..., 12).

Then write it on a ticket

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

Clock in free