LAG and LEAD

LAG returns a value from an earlier row in the window; LEAD from a later one. Before window functions, "compare each row to the one before it" required a self join on a row number, and it was miserable.

SQL▶ Run
SELECT order_id,
       placed_at,
       amount,
       LAG(amount)  OVER (ORDER BY placed_at, order_id) AS previous_amount,
       LEAD(amount) OVER (ORDER BY placed_at, order_id) AS next_amount
FROM orders
ORDER BY placed_at, order_id;

The first row has no previous, the last has no next, and both come back NULL — which is correct and is also the thing you have to handle.

Differences between consecutive rows

The most common use by far:

SQL▶ Run
SELECT order_id,
       placed_at,
       amount,
       ROUND(amount - LAG(amount) OVER (ORDER BY placed_at, order_id), 2) AS change,
       ROUND(100.0 * (amount - LAG(amount) OVER (ORDER BY placed_at, order_id))
             / NULLIF(LAG(amount) OVER (ORDER BY placed_at, order_id), 0), 1) AS pct_change
FROM orders
ORDER BY placed_at, order_id;

Two details worth stealing. NULLIF(…, 0) guards the division: one order has an amount of zero, and without the guard the percentage calculation would divide by it. And the first row's change is NULL rather than the amount itself — there is no previous row, and pretending otherwise would invent a hundred-percent rise every time a report starts.

The optional arguments

LAG(column, offset, default):

SQL▶ Run
SELECT order_id,
       amount,
       LAG(amount)       OVER (ORDER BY order_id) AS prev,
       LAG(amount, 2)    OVER (ORDER BY order_id) AS two_back,
       LAG(amount, 1, 0) OVER (ORDER BY order_id) AS prev_or_zero
FROM orders
ORDER BY order_id;

• offset defaults to 1 — how many rows back.
• default is returned instead of NULL when there is no such row. LAG(amount, 1, 0) gives a first row of 0, which makes a difference column arithmetic-safe.

LEAD takes the same three arguments, looking forward.

Within groups

PARTITION BY stops the comparison crossing group boundaries — essential, because comparing the last row of one customer to the first row of the next is a silent nonsense:

SQL▶ Run
SELECT customer_id,
       order_id,
       placed_at,
       LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at)
         AS previous_order_date,
       julianday(placed_at)
         - julianday(LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at))
         AS days_since_previous
FROM orders
ORDER BY customer_id, placed_at;

Days between one customer's consecutive orders. Every customer's first order shows NULL, which is right — there is nothing before it. Customers with a single order show NULL throughout.

julianday is SQLite's date arithmetic. PostgreSQL subtracts dates directly (placed_at - lag_date), MySQL has DATEDIFF, SQL Server has DATEDIFF(day, a, b). The window function is identical everywhere; only the date maths differs.

What LAG and LEAD are used for

• Change over time — revenue this month against last month.
• Elapsed time between events — as above; the basis of churn and retention analysis.
• Detecting state changes — WHERE status <> LAG(status) OVER (…) finds the exact rows where something flipped.
• Filling gaps forward — carrying the last known value into rows that have none.
• Finding overlaps — a booking whose start is before the previous booking's end.

Remember where you cannot use them

Like all window functions, LAG cannot appear in WHERE. To filter on a difference, compute it in a CTE and filter outside:

SQL▶ Run
WITH gaps AS (
  SELECT customer_id, order_id, placed_at,
         julianday(placed_at)
           - julianday(LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at))
           AS days_since_previous
  FROM orders
)
SELECT customer_id, order_id, placed_at, days_since_previous
FROM gaps
WHERE days_since_previous > 20
ORDER BY days_since_previous DESC;

Orders placed more than twenty days after that customer's previous one. The NULL first-orders are excluded automatically, because NULL > 20 is unknown — lesson 14's rule doing something useful for once.

Common mistakes

• No PARTITION BY — comparisons leak across groups and the numbers look plausible.
• Dividing by a LAG that can be zero — wrap it in NULLIF.
• Treating the first row's NULL as zero without meaning to — pass a default deliberately, or leave it NULL.
• LAG in WHERE — wrap the query.
• No tiebreaker in ORDER BY — with equal sort keys, "previous row" is arbitrary.

Interview question

Show each order alongside the number of days since that customer's previous order.

LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) and subtract. The interviewer is checking for the PARTITION BY — without it the query still runs and quietly compares across customers.

Check yourself

1. Find every month whose revenue fell against the previous month.
2. Why must the division in a percentage-change use NULLIF?
3. What does the third argument to LAG do?