The Growth Question in Disguise

Concepts covered: Date Truncation, Grouping, Sums & Averages

The question almost never arrives with the words 'period-over-period' in it. It arrives as 'is revenue growing', 'how is the East region trending', 'what did sign-ups do last quarter' or 'show me the lift since the redesign'. Every one of those reduces to the same calculation: take a figure for one period, take the same figure for the period before it, and put the 2 numbers on one row so they can be subtracted. The interviewer knows this. The first thing being scored is whether you know it too, and whether you say so before writing a line.

'growth', 'trend', 'trajectory', 'momentum': a value over consecutive periods, each compared with the one before
'month-over-month', 'week-over-week', 'quarter-over-quarter': the period length named for you
'change', 'delta', 'lift', 'drop': a difference between 2 periods, usually wanted as a percent
'is X up or down': the sign of that difference, with the size expected as a follow-up
'how did we do versus last month': the same question with the 2 periods named in plain words
'show me the numbers over time, with how much each moved': the output described rather than the calculation

Two Numbers on One Row

Say the shape of the answer out loud before touching the keyboard, because the shape settles half the design. For 'month-over-month revenue per region' one row of the answer is one region in one month, and that row carries 2 revenue figures: the month's own total and the previous month's total for the same region. Everything else, the difference and the percent, is arithmetic between those 2 columns. State it as 'one row per region per month, with this month's revenue and last month's revenue side by side', and the interviewer can relax, because you have named the grain and the comparison in 1 sentence.

region	month_start	revenue	prior_revenue	pct_change
East	2024-01-01	1000	NULL	NULL
East	2024-02-01	1550	1000	55.0%
East	2024-03-01	1100	1550	-29.0%

Read the East rows and the question answers itself: revenue rose 55% from January to February, then fell 29% in March. The table holds 3 rows because East had 3 months of sales, and the first row's prior_revenue is empty because January has no month before it in the data. Those 2 observations, the row count and the empty first cell, are what the interviewer will ask about later, so notice them now.

Roll Up Before You Compare

The trap in this round is working from the raw table. transactions holds 1 row per sale, so 'last month's revenue' does not exist in it as a value; it exists as hundreds of rows waiting to be added. A candidate who reaches straight for a comparison, joining the table to itself on 'the month before' or running LAG directly over sales rows, is comparing single sales with single sales. Joined, every January sale pairs with every February sale and the totals multiply; windowed, each row's 'prior' is the previous sale, often from the same afternoon. Both outputs print numbers that look like money and mean nothing.

The fix is an aggregation step before any comparison. DATE_TRUNC('month', txn_date) turns every date into the first day of its month, GROUP BY that and the region, and SUM(amount) collapses the sales into 1 row per region per month. Only once each period is a single row does 'the previous period' mean a single value. This step belongs in a CTE of its own, usually named monthly, so that the comparison that follows reads from a table where the grain is already right.

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)
)
SELECT * FROM monthly ORDER BY region, month_start;

The sentence that signals you parsed the prompt: 'This is a period-over-period question. I will roll transactions up to 1 row per region per month, use LAG to bring the previous month's total onto each row, then compute the change as the difference divided by the previous month.' The plan has 3 steps, named in order, and each maps to a CTE or a column in the query you are about to write.

Opening that loses time

Starts typing a join of transactions to transactions on the date
Discovers the row count is in the millions when asked
Backs out and re-plans with the clock running
Never states what 1 row of the answer is

Opening that scores

Names the pattern in the first sentence
States the grain: 1 row per region per month
Writes the rollup CTE before any comparison
Has the comparison column on screen within 2 minutes

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.