BeginnerSQL · 25 min

Period-over-Period: Beginner

Period-over-period is the question that powers every growth dashboard in every product analytics interview. Show me week-over-week active users. Compare this quarter to last. Month-over-month revenue. The English of the question rarely contains the words 'period-over-period.' It contains words like 'growth,' 'change,' 'trend,' or 'lift.' The candidate who reads the prompt and immediately names the pattern has already cleared the first hurdle. This lesson teaches you to spot it in the first ten seconds, write the canonical query from memory, and avoid the one missing-period bug that ships to every first-time author of one of these queries.

Spot a period-over-period question the moment the interviewer says "growth" or "change"

Write the canonical LAG-based query from muscle memory

Articulate what "prior period" means in a sentence the interviewer can repeat back

Avoid the silent missing-period bug that makes growth charts show wrong numbers

The Growth Rate Question in Disguise

Daily Life

Interviews

Spot when an interviewer is asking for period-over-period comparison hidden behind "trend," "growth," or "change."

Here is the question you will see in your first product analytics interview: 'show me month-over-month revenue growth per region.' Two minutes later the interviewer has a sense of whether you parsed the prompt correctly. The candidate who reads it, names the pattern, writes a clean three-step CTE, and traces through a small example has signaled three things: they read carefully, they know the pattern, and they reach for LAG without thinking. The candidate who tries to self-join the raw transactions table without pre-aggregating produces a query that returns plausible-looking inflated numbers.

You are being tested on period-over-period when you hear:

▸"growth rate," "change," "trend," "lift," "delta"
▸"compare this month/quarter/week to last"
▸"month-over-month," "week-over-week," "year-over-year"
▸"show the trajectory of X over time"
▸"what's the percent change from ... to ..."

What 'prior period' means, and how to spot the question

A period-over-period query computes a value (revenue, active users, signups) for each period, and pairs every row with the corresponding row from the prior period. The pairing rule defines what 'prior' means. For month-over-month, prior is the immediately previous calendar month. For year-over-year, prior is the same month one year ago. For week-over-week, prior is the previous seven-day window. The choice of pairing rule is a business decision; ask the interviewer which one they want before you write the query. Then, when you do start writing, say one sentence first: 'This is a period-over-period question. I will pre-aggregate to the period grain, then use LAG to get the prior period's value, then compute the growth rate as the difference divided by the prior value.' That sentence covers every part of the canonical query and tells the interviewer you parsed the prompt before reaching for SQL.

•Weak opening

Tries to self-join the raw transactions table directly
Returns inflated numbers from a fan-out
Has to be corrected when the interviewer asks about the row count
Loses time recovering from a misread of the question

✓Strong opening

Says 'this is period-over-period; I will pre-aggregate first, then use LAG'
Writes the monthly CTE before any comparison
Names the offset (LAG returns the immediately previous row in order)
Has the growth-rate expression on the page within ninety seconds

Why companies care

Period-over-period queries power every growth dashboard. Weekly active user retention. Monthly recurring revenue growth. Quarterly cohort performance. If you cannot write this query, an entire family of business questions is closed to you. That is why it appears in nearly every entry-level product analytics interview.

Truncating Dates into Periods First

Daily Life

Interviews

Build a period-over-period query by pre-aggregating to period grain then self-joining on offset dates.

The pre-aggregation is mandatory. Computing period-over-period over raw transactions without first rolling up to the period grain produces a fan-out: every transaction in the current month pairs with every transaction in the prior month. The numbers inflate, the dashboard shows nonsense, and the candidate spends the rest of the interview explaining what went wrong. The right shape is always two CTEs: one to compute the per-period totals, one to compare each period to its predecessor.

Why pre-aggregation is the floor, not an optimization:

▸Without it, every transaction pairs with every prior transaction (fan-out)
▸Without it, the numbers inflate by an unknown factor
▸With it, each period is a single row; the comparison is clean
▸Always aggregate to the period grain before any LAG or self-join

The query you should be able to write from memory

	/* Month-over-month revenue growth per region */
	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, 1) 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_delta,
	(
	revenue - prior_revenue
	) * 1 / NULLIF(prior_revenue, 0) AS growth_rate
	FROM compared
	ORDER BY region, month_start

Why each CTE matters

The monthly CTE pre-aggregates transactions to the (region, month) grain. The compared CTE pairs each month with its predecessor using LAG. The final SELECT computes the delta and the growth rate. Three steps, each with a single job. Reading the query top-down tells the story of the calculation; reading it bottom-up tells you the final shape the dashboard receives.

The pre-aggregation is the move most candidates skip. They write LAG directly against the raw transactions table, get a different row per transaction, and then try to aggregate the growth rates afterwards. That order is wrong; growth rate is not a per-transaction quantity. Always aggregate first, then compare.

LAG and NULLIF, the two operators that carry the query

LAG(revenue) OVER (PARTITION BY region ORDER BY month_start) returns the prior row's revenue value within the partition. The partition is region (so the LAG resets at each new region). The order is by month_start (so 'prior' means the immediately previous calendar month). For the first row in each partition, LAG returns NULL because there is no prior row. That NULL flows through to the growth rate, which becomes NULL for the first month per region. This is correct: the first month has no prior, so the growth rate is genuinely undefined. Then NULLIF(prior_revenue, 0) returns NULL when prior_revenue is zero, and returns prior_revenue otherwise. Dividing by NULL produces NULL, which is the right answer for 'growth from zero is undefined.' Without NULLIF, dividing by zero throws an error on most engines and produces infinity on others. NULLIF is the one-character defense against the division-by-zero bug.

TIP

Always wrap the divisor in NULLIF when computing a growth rate. It is two extra characters and it converts an error or a runtime infinity into a NULL the consumer can render as a dash. The dashboard's UI layer renders NULL as 'no comparison available'; that is the right rendering for a period with no prior.

Comparing This Period to the Last

Daily Life

Interviews

Replace the self-join with LAG() OVER (ORDER BY period) for sequential periods, and know when each approach wins.

Once you have written the LAG-based query, the interviewer will sometimes ask 'now do it without LAG.' The alternative is a self-join on offset dates. Both produce the same answer; both are common in real codebases; knowing both is the floor at this level. Knowing which one to reach for first is the move.

The self-join alternative

	/* Month-over-month growth via self-join, no window functions */
	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
	curr.region,
	curr.month_start,
	curr.revenue AS revenue,
	prev.revenue AS prior_revenue,
	curr.revenue - prev.revenue AS revenue_delta,
	(
	curr.revenue - prev.revenue
	) * 1 / NULLIF(prev.revenue, 0) AS growth_rate
	FROM monthly AS curr
	LEFT JOIN monthly AS prev
	ON curr.region = prev.region
	AND prev.month_start = curr.month_start - INTERVAL '1 month'

The self-join produces the same output as the LAG-based query. The differences are stylistic and semantic. The self-join references the monthly CTE twice; the reader's eyes track two copies of the same data. The LAG version references it once; the partition makes the per-region boundary explicit. For the immediately-previous-month case, LAG reads cleaner; both perform similarly on modern engines.

When the self-join wins

If the comparison is not immediately-previous but is 'this month vs the same month last year,' the self-join wins. Year-over-year comparison joins monthly to itself on (curr.month_start = prev.month_start + INTERVAL '12 months'). LAG cannot easily express that without a fixed twelve-row offset, which only works if the data has no gaps. Self-join handles the gap case correctly because it joins on a calendar offset, not on a row position.

•Reach for LAG when

The comparison is to the immediately previous period
The data has no missing periods within the comparison window
Performance matters and the engine has good window function support
Readability matters; the query reads top-to-bottom

✓Reach for the self-join when

The comparison is to a fixed date offset (same month last year, same day last week)
The data has missing periods that should remain as gaps
The engine has weak window function support
You need to expose extra columns from the prior row that LAG cannot return cleanly

The LEFT JOIN choice in the self-join

LEFT JOIN, not INNER. The first month in the dataset has no predecessor; INNER JOIN drops it. LEFT JOIN keeps it with NULL on the prev side, which flows through to a NULL growth rate. The dashboard then shows the first month with 'no comparison available' instead of silently dropping it. Defaulting to LEFT JOIN is the move; INNER JOIN truncates history without warning.

If the interviewer asks 'what happens to the first month?', the answer is the same for both approaches. LAG returns NULL for the first row of the partition. LEFT JOIN returns NULL on the prev side. Both produce a NULL growth rate. The first month is honestly represented as 'no prior data,' which is the right rendering.

Situation	Phrasing that flatlines	Phrasing that lands
You see a growth or change question	"I'll join transactions to themselves."	"This is period-over-period; I'll pre-aggregate to (region, month) inside a CTE, then LAG over that."
The interviewer asks 'what about the first month'	"It'll show wrong, but I can filter it out."	"LAG returns NULL for the first row per partition; the growth rate becomes NULL, and the dashboard renders it as 'no comparison available.'"
The prompt is 'growth vs same month last year'	"I'll use LAG with a 12-row offset."	"LAG by row position breaks if any month is missing; I'd self-join the monthly CTE on month_start = prev.month_start + INTERVAL '12 months' so it's a calendar offset, not a positional one."
You need to divide by the prior period	"I'll add a CASE WHEN prior = 0."	"NULLIF(prior_revenue, 0) on the divisor; that converts zero priors to NULL growth, which is the honest answer."
The data has gaps	"The query still runs."	"Gaps silently shift the comparison; March vs January reads as a normal MoM when it isn't. I'd build a date spine and LEFT JOIN actuals onto it."

Computing Percent Change Safely

Daily Life

Interviews

Handle gaps where a period has no data: generate a date spine, LEFT JOIN actuals, and COALESCE to zero.

The biggest bug in a beginner period-over-period query is missing periods. The query looks right, the SQL is clean, the numbers in the dashboard look plausible , but a region that had no transactions in February shows March's growth computed against January, not against zero. The bug is silent and the dashboard's number is wrong by an unknown factor. This section is about recognizing the bug and fixing it the right way.

The bug, stated plainly, and the fix

Suppose a region had revenue in January ($100k), no transactions in February, and revenue in March ($300k). The monthly CTE produces two rows: January and March. LAG(revenue) for March returns January's revenue ($100k), not February's (which would have been $0). The growth rate for March is computed as ($300k - $100k) / $100k = +200%, when the honest answer is 'March vs February, where February was zero, so the growth is undefined or infinite.' The dashboard shows +200% growth. It is wrong.

At Airbnb in 2021, the weekly active host dashboard for a then-new market silently shifted week-over-week growth comparisons every time the market had a holiday-driven booking gap. The query was a clean LAG over a weekly CTE with no date spine; a week of zero activity dropped out of the CTE, and the LAG paired the next active week with the one before the gap. The growth chart looked normal, except that one row was secretly a two-week comparison instead of a one-week one. The bug shipped to a board-deck slide before anyone caught it; the postmortem named the fix as 'always materialize the period axis before the LAG.' That sentence, in those words, has shown up in three subsequent interview rounds for that team.

	/* Period-over-period with an explicit date spine */
	WITH date_spine AS (
	SELECT
	r.region,
	g.month_start
	FROM (
	SELECT DISTINCT
	region
	FROM transactions
	) AS r
	CROSS JOIN (
	SELECT
	GENERATE_SERIES(
	DATE_TRUNC(
	'month',
	(
	SELECT
	MIN(txn_date)
	FROM transactions
	)
	),
	DATE_TRUNC(
	'month',
	(
	SELECT
	MAX(txn_date)
	FROM transactions
	)
	),
	INTERVAL '1 month'
	) AS month_start
	) AS g
	),
	monthly AS (
	SELECT
	s.region,
	s.month_start,
	COALESCE(SUM(t.amount), 0) AS revenue
	FROM date_spine AS s
	LEFT JOIN transactions AS t
	ON t.region = s.region
	AND DATE_TRUNC('month', t.txn_date) = s.month_start
	GROUP BY s.region, s.month_start
	)

	SELECT
	region,
	month_start,
	revenue,
	LAG(revenue, 1) OVER (
	PARTITION BY region
	ORDER BY month_start
	) AS prior_revenue,
	(
	revenue - LAG(revenue, 1) OVER (
	PARTITION BY region
	ORDER BY month_start
	)
	) * 1 / NULLIF(
	LAG(revenue, 1) OVER (
	PARTITION BY region
	ORDER BY month_start
	),
	0
	) AS growth_rate
	FROM monthly

The date_spine CTE produces every (region, month) combination. The monthly CTE LEFT JOINs actuals onto the spine; missing periods become rows with revenue = 0. The LAG now sees the zero for February explicitly. March's growth rate is computed against $0, which produces NULL (because NULLIF avoids the division by zero). The dashboard now shows March with 'no comparison available' rather than the misleading +200% from before.

Zero vs NULL: a business question

Should missing periods count as zero, or as missing? The answer depends on the business context. For a region that legitimately had no sales activity, zero is honest. For a data quality issue where the ingestion failed for that period, missing should remain missing (NULL). The dashboard should render zero as '$0' and NULL as 'data not available.' Ask the interviewer which semantics the consumer wants before defaulting to one or the other.

The date spine is the single move that most distinguishes the candidate who has shipped reporting from the candidate who has not. Most candidates have heard of the technique; few reach for it by default. Reaching for it without being told is what tells the interviewer you have been on the wrong side of a 'why does our growth chart show -100% in February' Slack ping.

TIP

When you write the date spine, narrate why each step exists. 'CROSS JOIN of regions and months gives me every combination. LEFT JOIN onto transactions fills in the actual values. COALESCE turns missing values into zero, or I could keep them as NULL depending on the business contract.' That narration is what proves you understand the technique, not just memorized it.

Sketching the Two Rows You Subtract

Daily Life

Interviews

Calculate period-over-period across multiple segments simultaneously and discuss the fan-out problem.

Once the single-dimension version works, the interviewer extends. 'Now do it by region and product.' 'Now by region, product, and channel.' Each new dimension multiplies the partition count. The query is one extra column in the GROUP BY and PARTITION BY; the cost grows multiplicatively. Knowing where the cost comes from is what separates a beginner answer that scales from one that times out.

The two-dimension version

	/* Month-over-month growth by region and product */
	WITH monthly AS (
	SELECT
	region,
	product,
	DATE_TRUNC('month', txn_date) AS month_start,
	SUM(amount) AS revenue
	FROM transactions
	GROUP BY region, product, DATE_TRUNC(
	'month',
	txn_date
	)
	),
	compared AS (
	SELECT
	region,
	product,
	month_start,
	revenue,
	LAG(revenue, 1) OVER (
	PARTITION BY region, product
	ORDER BY month_start
	) AS prior_revenue
	FROM monthly
	)

	SELECT
	region,
	product,
	month_start,
	revenue,
	prior_revenue,
	(
	revenue - prior_revenue
	) * 1 / NULLIF(prior_revenue, 0) AS growth_rate
	FROM compared
	ORDER BY region, product, month_start

Two columns in the GROUP BY (region, product), two columns in the PARTITION BY (matching). The query structure is identical to the single-dimension case. The output volume grows: if you had 10 regions and 50 products, the monthly CTE now has roughly 10 × 50 × 24 = 12,000 rows over a two-year window, versus 10 × 24 = 240 rows for the region-only case. The 50× growth is real cost when this query runs frequently.

Why the cost matters, and the fan-out trap

Each additional dimension multiplies the partition count. The window function shuffles the data by the partition key before computing LAG. More partitions means a wider shuffle. On a modern engine the cost is still linear in the output row count, but the constant factor grows with the partition cardinality. For a dashboard that refreshes hourly, the difference between a single-dimension query (region) and a four-dimension query (region, product, channel, market_segment) can be the difference between a 30-second refresh and a 5-minute one. Name the cost when you extend to multiple dimensions: 'this fans out by the cardinality of each dimension; I would check the partition count before deploying.' The related trap is doing the multiplication in the wrong place. If the question is 'month-over-month revenue per region,' do not be tempted to add a JOIN to a product or customer dimension table at the wrong grain. Joining a monthly revenue table to a daily product dimension creates a fan-out that inflates revenue. The right pattern is to pre-aggregate everything you need into the monthly CTE first, then never join to anything else. The growth-rate output should derive only from the pre-aggregated CTE.

•Common multi-dimension mistakes

Adds dimensions to the PARTITION BY but not to the GROUP BY
Joins the monthly aggregate to a daily dimension table at the wrong grain
Returns the cartesian product when the data is sparse
Skips the date spine because the query already has multiple dimensions

✓The clean multi-dimension answer

Add the dimensions to both GROUP BY and PARTITION BY in matching order
Pre-aggregate every dimension you need before any JOIN or window function
Apply the date spine to the cartesian product of all dimensions × months
Filter sparse combinations inside the CTE if the output is too large

The closing summary

Close with a three-sentence wrap. 'I pre-aggregated to the (region, month) grain inside a monthly CTE, then used LAG over that CTE to compute the prior period's revenue. NULLIF on the divisor handles the division-by-zero case for the first period and for periods with zero prior revenue. For missing periods, I would generate a date spine of all (region, month) combinations and LEFT JOIN the actuals onto it, so the LAG sees explicit zeros instead of silently shifting the comparison.' Three sentences. Each names a different rubric item: the pattern, the divide-by-zero defense, the missing-period defense. The shape generalizes.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a product analytics phone screen. The interviewer asks: 'Show me month-over-month revenue growth per region.'

You say: 'This is a period-over-period query. I will pre-aggregate to (region, month), then use LAG to compute the prior month, then compute the growth rate.'

You write three CTEs: monthly aggregate, compared (with LAG), and the final SELECT with the growth-rate expression wrapped in NULLIF.

You trace through a small example: West region with $100k in January and $130k in February. Growth is 30k/100k = 30%. February has prior_revenue from LAG; January has NULL prior_revenue, so its growth is NULL.

Follow-up: 'What if a region had no revenue in March?' You say: 'Without a date spine, the LAG would silently shift March to compare against February instead of the missing month. The fix is to build a date spine of every (region, month) and LEFT JOIN actuals onto it so missing periods become explicit zero rows.'

KEY TAKEAWAYS

Pre-aggregation to the period grain is the floor, not an optimization. Running LAG against raw transactions pairs every current-period row with every prior-period row and inflates the numbers; the correct shape is one CTE for the per-period totals and one to compare each period to its predecessor.

LAG(revenue) OVER (PARTITION BY region ORDER BY month_start) is the standard pairing tool: the partition resets the comparison at each region and the order defines what prior means. The first period per partition returns NULL, which is the honest answer.

Reach for a self-join instead of LAG when the comparison is a calendar offset rather than the immediately previous row, such as year over year on curr.month_start = prev.month_start + INTERVAL '12 months', because a fixed row offset only lands correctly when the data has no gaps.

Use LEFT JOIN rather than INNER JOIN in the self-join form so the earliest period survives with a NULL prior side instead of being dropped, and wrap the divisor in NULLIF(prev_revenue, 0) so a zero prior period yields NULL rather than an error.

A period with no rows disappears from the aggregate CTE and silently turns the next comparison into a two-period jump. Build a date spine of every entity and period combination, LEFT JOIN the actuals onto it, and decide with the consumer whether a missing period means zero activity or missing data.

Each extra dimension multiplies partition cardinality: 10 regions by 50 products over 24 months is roughly 12,000 rows against 240 for the region-only version, and the window shuffle widens with it, which matters for anything refreshing hourly.

Week-over-week growth is a three-line query once you see the join

Category: SQL
Difficulty: beginner
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: The Growth Rate Question in Disguise, Truncating Dates into Periods First, Comparing This Period to the Last, Computing Percent Change Safely, Sketching the Two Rows You Subtract

Lesson Sections

The Growth Rate Question in Disguise (concepts: sqlLagLead)
What 'prior period' means, and how to spot the question A period-over-period query computes a value (revenue, active users, signups) for each period, and pairs every row with the corresponding row from the prior period. The pairing rule defines what 'prior' means. For month-over-month, prior is the immediately previous calendar month. For year-over-year, prior is the same month one year ago. For week-over-week, prior is the previous seven-day window. The choice of pairing rule is a business deci
Truncating Dates into Periods First (concepts: sqlSelfJoin)
The pre-aggregation is mandatory. Computing period-over-period over raw transactions without first rolling up to the period grain produces a fan-out: every transaction in the current month pairs with every transaction in the prior month. The numbers inflate, the dashboard shows nonsense, and the candidate spends the rest of the interview explaining what went wrong. The right shape is always two CTEs: one to compute the per-period totals, one to compare each period to its predecessor. The query y
Comparing This Period to the Last (concepts: sqlLagLead)
Once you have written the LAG-based query, the interviewer will sometimes ask 'now do it without LAG.' The alternative is a self-join on offset dates. Both produce the same answer; both are common in real codebases; knowing both is the floor at this level. Knowing which one to reach for first is the move. The self-join alternative The self-join produces the same output as the LAG-based query. The differences are stylistic and semantic. The self-join references the monthly CTE twice; the reader's
Computing Percent Change Safely (concepts: sqlCrossJoin)
The biggest bug in a beginner period-over-period query is missing periods. The query looks right, the SQL is clean, the numbers in the dashboard look plausible , but a region that had no transactions in February shows March's growth computed against January, not against zero. The bug is silent and the dashboard's number is wrong by an unknown factor. This section is about recognizing the bug and fixing it the right way. The bug, stated plainly, and the fix At Airbnb in 2021, the weekly active ho
Sketching the Two Rows You Subtract (concepts: sqlPartitionBy)
Once the single-dimension version works, the interviewer extends. 'Now do it by region and product.' 'Now by region, product, and channel.' Each new dimension multiplies the partition count. The query is one extra column in the GROUP BY and PARTITION BY; the cost grows multiplicatively. Knowing where the cost comes from is what separates a beginner answer that scales from one that times out. The two-dimension version Two columns in the GROUP BY (region, product), two columns in the PARTITION BY