Chapter 4 · 9 min
Lag, lead, and period comparisons
lag and lead read a value from another row in the same partition: lag looks backwards,
lead looks forwards. Both take an optional offset and default, and both need an ordering that
fully determines the row sequence.
WITH monthly AS (
SELECT date_trunc('month', o.order_date)::date AS month,
sum(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY 1
)
SELECT
month,
round(revenue, 2) AS revenue,
round(lag(revenue) OVER (ORDER BY month), 2) AS previous_revenue,
round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
/ nullif(lag(revenue) OVER (ORDER BY month), 0), 2) AS growth_percent
FROM monthly
ORDER BY month ASC;
The first month has no previous row, so lag returns NULL and the growth calculation returns
NULL too. That is correct, not a bug: a first period has no comparison. Supplying a default would
turn it into zero, which reads as “no growth” and is usually the wrong claim.
Division by zero and missing rows
A percentage change divides by the previous value, so guard the denominator with nullif(previous, 0). Without it, a period that follows a zero-revenue period raises a division-by-zero error. The
same guard applies to share-of-total calculations.
First-touch attribution
“Which channel first brought this customer in?” is a first-row-per-partition question. The
interview-standard answers are row_number() filtered to 1, or Postgres’s DISTINCT ON:
SELECT DISTINCT ON (customer_id)
customer_id, order_id, channel, order_date
FROM orders
ORDER BY customer_id, order_date ASC, order_id ASC;
DISTINCT ON keeps the first row of each partition according to its ORDER BY, so the ordering
must start with the partition key. Both forms need a tiebreaker (order_id) to be deterministic.
Repeat purchases and time between events
A repeat-purchase question usually wants the gap between consecutive events. Compute the previous
event date with lag, subtract, and compare the difference with the threshold:
SELECT customer_id, order_date,
order_date - lag(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date, order_id
) AS days_since_previous
FROM orders;
Date subtraction on date values yields an integer day count. Cast or round deliberately if the
columns are timestamps, and state the boundary in the answer: “within 30 days” includes day 30.
Ordering ties
If two events share a date, lag can read either one unless the ordering includes a unique column.
Add the event id as the final ordering column. A window function over an ambiguous order is the most
common source of a result that passes once and fails on a rerun.