Period-over-Period: Beginner

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.

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.

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.

PARTITION BY region inside the OVER clause splits that 1 list into 1 list per region, and the window function runs separately inside each. LAG looking back from West's January finds nothing, because nothing in West's list comes before it, and returns NULL. Looking back from West's February it finds West's January, 1000, which is the figure the question wants. The partition is the SQL spelling of 'against its own past', and saying that sentence as you type the clause tells the interviewer that the clause is deliberate.

The partition list is whatever the question says 'per' or 'by'. 'Per region and product' means PARTITION BY region, product, and the rollup's GROUP BY carries the same 2 columns, because the rollup and the comparison have to agree on what 1 row is. A rollup grouped by region alone and a window partitioned by region and product would hand the window rows where product does not exist, so the pair is always written together: the columns after GROUP BY in the rollup are the columns after PARTITION BY in the window, with the period column left out of the partition and placed in ORDER BY.

The animation's monthly table carries 3 rows for East: Desk in January at 1000, Desk in February at 1250, and Lamp in February at 300. With PARTITION BY region, product, Desk's February row receives 1000 as its prior, because Desk's January is the row before it inside the Desk sequence. Lamp's February row receives NULL, because Lamp has no January row at all. A partition on region alone would have handed Lamp whichever East row the tie on 2024-02-01 happens to place before it, Desk's February at 1250 or Desk's January at 1000. Both are another product's revenue, so the 76% or 70% collapse it reports is fiction either way.

A different follow-up probes whether you understand that the window does not reduce rows. GROUP BY collapses many rows into 1; PARTITION BY keeps every row and only decides which neighbours a row can see. The output of the comparison step has the same 6 rows as monthly, 1 new column wider. Candidates who expect the partition to produce 1 row per region have confused the 2 clauses, and the interviewer will ask for the row count to find out.

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.

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.

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'.

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.

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.

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.

Everything that goes wrong in this query goes wrong in the pairing: the row LAG hands you is a row from the wrong region, from a month that is not the previous one, or from a different position than you pictured. The defence is a habit you can perform in the room in under a minute: before writing the window clause, write out 2 rows by hand, the row you are computing and the row its prior should come from, with every column filled in. If you cannot write the second row, you do not yet know what the clause should say.

The sketch forces 3 answers out of you. The prior row shares the region, so PARTITION BY region. The prior row is the month immediately before, so ORDER BY month_start with an offset of 1. The expected result for East in March is 1100 against 1550, a fall, so when the query runs and that row shows a positive change, you know the pairing is wrong before the interviewer does.

The second wrong pairing comes from the ORDER BY. A rollup that produced a month label with TO_CHAR(txn_date, 'Month'), or stored the month as text, sorts alphabetically, and April, February, January, March is the order the window walks. February's 'prior' becomes April. Ordering by revenue instead of the date is the other variant, usually a slip when the ORDER BY from the final query is pasted into the window. In both cases the sketch's prior row has the wrong month on it, which is visible the moment you compare it against the row you drew.

East's February now reads as a change from 1400 to 1550, a rise that pairs it with a month 2 months later. Keep the truncated date in the rollup even when the report wants a label. DATE_TRUNC gives a value that sorts as time, and a label column formatted from it can sit beside it for display. The window always orders by the date.

Sometimes the sketch itself is the warning. You draw East's March and reach for East's February, and the monthly table has no February row for East because the region sold nothing that month. LAG will hand March the January row without complaint, and the change will be labelled month-over-month while spanning 2 months. The expectation here is to notice: 'the previous row is not the previous month, so this growth rate is comparing across a gap'. Say that, and the interviewer hears someone who checks the rows rather than trusting the clause. How to fill the gap is a longer conversation, and naming it is what is scored.

The sketch costs 30 seconds and the interviewer sees you doing it, which reads as method rather than hesitation.