Sketching the Rows You Subtract

Concepts covered: Row Offsets, Window Partitioning, Sorting Results

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.

• Same group: every column in the partition matches between the 2 rows
• Adjacent period: the prior row's month is 1 period before, with nothing between
• Date order: the ORDER BY column is a date or timestamp, never a label or a measure
• Direction: the prior is earlier, so LAG; a later row would be LEAD
• Expected sign: you know whether this row should read as up or down before running

The Partition Left Out

SELECT region, month_start, revenue,
  LAG(revenue) OVER (ORDER BY region, month_start) AS prior_revenue
FROM monthly

The animation orders the 4 rows by region and then month, with no partition. East's February correctly receives East's January, 1000. West's January, with nothing of West's before it, receives 1550: East's February, the row above it in the single ordered list. West's February then receives 1000 from West's January, which is right, so the output has 1 wrong cell in 4 and 3 right ones. The sketch catches it: the prior row you drew for West's January was blank, and the query gave it a number.

Ordering by the Wrong Column

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.

When the Sketch Shows a Hole

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.

Do

• Roll up to 1 row per group per period in a CTE before any LAG
• Say the grain aloud: 1 row per region per month, with this month and last month side by side
• Partition by every 'per' column and order by the truncated date
• Divide the change by the prior period, wrapped in NULLIF, multiplied by 1.0
• Sketch the 2 rows for 1 group before writing the OVER clause

Avoid

• Don't run LAG or a self-join over raw transaction rows
• Don't replace the first period's NULL with 0 or filter the row away
• Don't order the window by a month label or by the measure
• Don't divide by the current period; the result looks right and is wrong
• Don't report a fraction and call it a percent without saying which

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.