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.