LAG, LEAD, FIRST_VALUE: peeking at the row before or after
Comparing a row to its neighbor used to mean joining a table to itself. LAG and LEAD let a row see the previous or next row directly, in one line.
Sometimes you want to compare a row to the one right before or after it: "how much did revenue
change from yesterday?" LAG and LEAD do exactly that, without a self-join.
🎯 Explain Like I'm Hired
LAGreaches back and pulls a value from the previous row into your current row.LEADreaches forward and pulls from the next row. Both do this without needing a second copy of the table joined to itself (the trick you'd otherwise need, see the self-join in the JOINs article). Example: "how many days between each customer's orders?" isorder_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), one line, instead of a self-join hunting for "the previous order."
SELECT
customer_id,
order_date,
amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change
FROM orders;
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.