Comparing This Period to the Last

Concepts covered: Row Offsets, Common Table Expressions, Date Truncation

SQL evaluates 1 row at a time. An expression in a SELECT can read any column on the row it is evaluating and none from the row above, which is the whole difficulty of this question: the subtraction revenue - prior_revenue needs 2 months on 1 row, and the rollup put them on 2. Something has to copy the earlier value across. LAG is the function that does it: for each row, it looks at the row 1 position earlier in the window's order and returns that row's value of whatever column you name.

Reading the LAG Clause

The Query, Stage by Stage

WITH monthly AS (
  SELECT
    region,
    DATE_TRUNC('month', txn_date) AS month_start,
    SUM(amount)                   AS revenue
  FROM transactions
  GROUP BY region, DATE_TRUNC('month', txn_date)
),
compared AS (
  SELECT
    region,
    month_start,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY region
      ORDER BY month_start
    ) AS prior_revenue
  FROM monthly
)
SELECT
  region,
  month_start,
  revenue,
  prior_revenue,
  revenue - prior_revenue AS revenue_change
FROM compared
ORDER BY region, month_start;

The monthly CTE is the rollup from the opening: 1 row per region per month. The compared CTE adds 1 column and no rows: prior_revenue, filled by LAG from the row above inside each region. The final SELECT does arithmetic between 2 columns that now sit on the same row. Writing it in 3 stages costs a few lines and buys 2 things the interviewer values: every intermediate table can be run on its own to check its row count, and the subtraction reads as plain column arithmetic with no window syntax around it.

WITH monthly AS (
  SELECT DATE_TRUNC('month', txn_date) AS month_start,
         SUM(amount) AS revenue
  FROM transactions
  GROUP BY DATE_TRUNC('month', txn_date)
)
SELECT month_start, revenue,
  LAG(revenue) OVER (ORDER BY month_start) AS prior_revenue
FROM monthly

The animation drops the region to keep the stage small and plays the same 2 stages over 4 sales. January's 2 sales, 1200 and 800, fold into 1 row at 2000; February's single sale becomes 2400; March's becomes 1700. Then LAG walks the 3 monthly rows in date order: January looks back and finds nothing, so its prior_revenue is NULL; February looks back and copies 2000; March looks back and copies 2400. Watch which row each copied cell flies out of, because that motion is what you will describe in the room: 'each row reaches back 1 month and brings that month's total forward'.

The First Row Has No Partner

January's prior_revenue is NULL, and so is its change, because NULL minus anything is NULL. This is correct and the interviewer will test whether you think so. The weak answer is 'I'd filter that row out' or 'I'd treat it as zero'. Zero would report January as 2000 of growth from nothing, which is a claim about December that the data does not support. The strong answer: 'the first month of each region has no comparison, so it stays in the output with a blank change, and the report shows a dash'. Keeping the row also keeps the row count equal to monthly, which is the check you promised.

A common follow-up is 'what if I wanted next month on the row instead'. LEAD(revenue) with the same OVER clause looks 1 row forward rather than back, so the last month of each region becomes the NULL. The arithmetic flips: LEAD answers 'how much did next month move from this one', which is the same change attributed to a different row. For a trend report the convention is to attach the change to the later month, which is LAG, and saying 'the change belongs to the month it happened in' is the reason to give.

About this lesson

This section is part of the Period-over-Period: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.