Which Period Is the Denominator

Concepts covered: Null Substitution

Once the 2 values share a row the change is 1 subtraction, and the percent is that subtraction divided by 1 of the 2 values. Which one is the question this section exists for, because both divisions produce a believable number and only 1 is the growth rate. Growth is measured against where you started: February's growth over January is February minus January, divided by January. The earlier period is the denominator, every time, and 'divided by the prior period' is a phrase to say aloud as you type it.

SELECT
  region,
  month_start,
  revenue,
  prior_revenue,
  revenue - prior_revenue                                    AS revenue_change,
  (revenue - prior_revenue) * 1.0 / NULLIF(prior_revenue, 0) AS pct_change
FROM compared
ORDER BY region, month_start;

Over the earlier month, February is up 20%: 400 more on a base of 2000. Divided by the current month instead, the same 400 reads as 16.7%, and March's fall of 700 reads as 41.2% instead of 29.2%. Neither wrong figure is absurd, which is what lets the error survive review; a dashboard built on the wrong denominator reports every rise too small and every fall too large, and nobody notices until a finance team recomputes a number by hand.

Reading What Comes Back

The expression returns a fraction: 0.20 for February, with 0 meaning flat, a positive value meaning the period grew and a negative value meaning it shrank. If the interviewer wants a percent column, multiply by 100 and round, ROUND((revenue - prior_revenue) * 100.0 / NULLIF(prior_revenue, 0), 1), and say which you are producing, because 'growth of 0.2' and 'growth of 20' are both plausible on a screen and only the column name tells them apart. Read a negative value as 'down by', so -0.292 in March is 'revenue down 29.2% from February', with the base month named in the sentence.

The denominator also explains a fact worth stating when asked to interpret a result: a fall and the rise that undoes it are different percents. March fell 29.2% from 2400 to 1700. Climbing back to 2400 next month would be a change of 700 on a base of 1700, a larger percent than the fall, because the base shrank. Candidates who say 'it would need to grow 29% to recover' have divided by the wrong month in their head.

• +41.2%

Two Guards on the Division

NULLIF(prior_revenue, 0) returns NULL when the prior is 0 and the prior otherwise. Dividing by NULL yields NULL, which is the honest result: growth from a month that earned nothing has no percent, and reporting NULL lets the dashboard show a dash. Without it, the month after one where refunds cancelled out every sale, so SUM(amount) came to exactly 0, raises a division-by-zero error on PostgreSQL and most warehouses, and the whole report fails on 1 row. A month with no sales at all is a different case: it has no row in monthly, so LAG reaches past it to an older month, which is the gap problem the sketch catches and NULLIF cannot. The interviewer who asks 'what if last month was zero' is checking for this 1 function, and 'I would NULLIF the denominator' is the complete answer.

* 1.0 guards against integer division. If amount is an integer column, SUM(amount) is an integer, and on PostgreSQL an integer divided by an integer truncates toward zero: 400 / 2000 becomes 0, March's -700 / 2400 also becomes 0, and so does every change smaller than the prior month, rises and falls alike. Only a change of at least the whole prior survives, so a doubling shows as 1. Nearly every rate in the report reads 0, and nothing errors. Multiplying the numerator by 1.0 promotes the arithmetic to decimal before the division. A CAST(revenue - prior_revenue AS DECIMAL(18, 4)) does the same more verbosely; either is fine, and mentioning the hazard unprompted is a point in your favour.

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.