{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"lag-lead-and-change","title":"LAG and LEAD: the previous row, and the change since it","url":"https://addaly.com/learn/data-and-sql/lag-lead-and-change","position":38,"of":66,"minutes":9,"answer":"LAG and LEAD read a fixed number of rows back or forward in the window's order, not a fixed distance in time.","keyIdea":"LAG and LEAD read a fixed number of rows back or forward in the window's order, not a fixed distance in time, and last_value returns the current row unless the frame is extended to UNBOUNDED FOLLOWING."},"body":{"format":"markdown","text":"## Reading the row before this one\n\nMonth-over-month growth, days since the last order, whether a price went up or down: every one of these needs a row to see its predecessor. `lag()` does exactly that.\n\n```sql\nSELECT month, revenue,\n       lag(revenue) OVER (ORDER BY month)              AS prev_revenue,\n       revenue - lag(revenue) OVER (ORDER BY month)    AS change,\n       (revenue - lag(revenue) OVER (ORDER BY month))\n         / nullif(lag(revenue) OVER (ORDER BY month), 0) AS pct_change\nFROM monthly_revenue;\n```\n\n`lag(revenue)` returns the value of `revenue` from the row one position earlier in the window's order. The first row has no predecessor and gets NULL, which is why the percentage change is guarded with `nullif`. `lag(revenue, 12)` reaches twelve rows back, which is the same month last year if, and only if, every month is present. `lag(revenue, 1, 0)` supplies a default instead of NULL. `lead()` is the mirror image and looks forward.\n\n## Per-entity, with a partition\n\nAdd a partition and each entity gets its own sequence:\n\n```sql\nSELECT customer_id, placed_at,\n       placed_at - lag(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at, order_id)\n         AS days_since_last_order\nFROM orders;\n```\n\nA customer's first order gets NULL, every later one gets the gap. `avg(days_since_last_order)` over that result is the average reorder interval, and `lead()` in the same shape gives \"days until the next order\", which is what you compute when the question is whether a customer has gone quiet compared with their own habit.\n\n## The gap is in rows, not in time\n\n`lag()` looks back one *row*. If a customer has months with no orders, the previous row is not the previous month; it is whenever they last ordered. For \"revenue in the previous calendar month\" on a table with gaps, either join to a spine first so that every month exists as a row, or look the value up by date with a self-join on `month - interval '1 month'`. The window over a spine is cleaner and is the usual answer. The mistake to avoid is computing month-over-month change with `lag()` on a table that skips a month, which reports a two-month change as if it were one.\n\n## first_value, last_value, and a trap\n\n`first_value(price) OVER (PARTITION BY product_id ORDER BY changed_at)` gives every row the product's original price. `last_value()` should, by symmetry, give the latest, and it does not:\n\n```sql\nlast_value(price) OVER (PARTITION BY product_id ORDER BY changed_at)\n-- returns each row's OWN price\n```\n\nThe reason is the default frame from the previous lesson. It runs from the start of the partition to the current row, so the \"last\" value in the frame is always the current one. To see the genuine last value the frame has to extend to the end:\n\n```sql\nlast_value(price) OVER (PARTITION BY product_id ORDER BY changed_at\n                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\n```\n\nThis is the most searched-for window function bug there is, and it is the frame every time. `nth_value(price, 2)` has the same behaviour and the same fix.\n\n## Direction and streaks\n\nComparing a row with its predecessor is how you classify movement: `CASE WHEN price > lag(price) OVER w THEN 'up' WHEN price < lag(price) OVER w THEN 'down' ELSE 'flat' END`. Count the 'up' rows and you have how often the price rose. Flag the rows where the direction changed and take a running sum of the flags, and you have numbered the runs, which is the next lesson's method for streaks and sessions.\n\n## What lag cannot do\n\nIt looks back a fixed number of rows in one ordering. \"The previous order from the same branch\" when the window is partitioned by customer needs a different partition and therefore a second window, and \"the previous order over ₹1,000\" needs a filter before the window, because `lag()` cannot skip rows conditionally. A `lag()` on a filtered CTE handles the second case; the first is two windows in one query, which the first lesson of this module showed is fine.\n\n## The habit\n\nEvery `lag()` answers two questions you should be able to state: ordered by what, and partitioned by what. And any `last_value()` without an explicit frame is a bug until proved otherwise."},"check":{"question":"`last_value(status) OVER (PARTITION BY ticket_id ORDER BY changed_at)` is meant to show every row of a ticket's history alongside the ticket's final status, but each row shows its own status instead. What fixes it?","options":[{"text":"Replace `last_value` with `first_value` and reverse the ORDER BY to `changed_at DESC`, because last_value is only reliable on the first row of a partition.","correct":false,"tempting":"Reversing the order makes first_value return the final status, which works, but for the wrong stated reason; last_value is reliable once its frame reaches the end of the partition."},{"text":"Add `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`, because the default frame ends at the current row.","correct":true},{"text":"Remove the ORDER BY, because last_value only looks at the whole partition when the rows are unordered.","correct":false,"tempting":"Without ORDER BY the frame is the whole partition but \"last\" is then undefined, so the value returned is arbitrary."},{"text":"Use `max(changed_at)` in a GROUP BY and join back, since window functions cannot see rows after the current one.","correct":false,"tempting":"Windows can see following rows whenever the frame includes them; the join-back works but is a workaround for a one-clause fix."}]},"prev":{"slug":"running-totals-and-frames","title":"Running totals, and the frame you did not know you set","url":"https://addaly.com/learn/data-and-sql/running-totals-and-frames"},"next":{"slug":"moving-averages","title":"Moving averages: seven rows is not seven days","url":"https://addaly.com/learn/data-and-sql/moving-averages"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'LAG and LEAD: the previous row, and the change since it', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/lag-lead-and-change (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"LAG and LEAD: the previous row, and the change since it","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"LAG and LEAD read a fixed number of rows back or forward in the window’s order, not a fixed distance in time, and last_value returns the current row unless the frame is extended to UNBOUNDED FOLLOWING.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"reading-the-row-before-this-one","runs":[{"text":"Reading the row before this one"}]},{"t":"p","runs":[{"text":"Month-over-month growth, days since the last order, whether a price went up or down: every one of these needs a row to see its predecessor. "},{"text":"lag()","code":true},{"text":" does exactly that."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT month, revenue,\n       lag(revenue) OVER (ORDER BY month)              AS prev_revenue,\n       revenue - lag(revenue) OVER (ORDER BY month)    AS change,\n       (revenue - lag(revenue) OVER (ORDER BY month))\n         / nullif(lag(revenue) OVER (ORDER BY month), 0) AS pct_change\nFROM monthly_revenue;"},{"t":"p","runs":[{"text":"lag(revenue)","code":true},{"text":" returns the value of "},{"text":"revenue","code":true},{"text":" from the row one position earlier in the window’s order. The first row has no predecessor and gets NULL, which is why the percentage change is guarded with "},{"text":"nullif","code":true},{"text":". "},{"text":"lag(revenue, 12)","code":true},{"text":" reaches twelve rows back, which is the same month last year if, and only if, every month is present. "},{"text":"lag(revenue, 1, 0)","code":true},{"text":" supplies a default instead of NULL. "},{"text":"lead()","code":true},{"text":" is the mirror image and looks forward."}]},{"t":"h2","id":"per-entity-with-a-partition","runs":[{"text":"Per-entity, with a partition"}]},{"t":"p","runs":[{"text":"Add a partition and each entity gets its own sequence:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT customer_id, placed_at,\n       placed_at - lag(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at, order_id)\n         AS days_since_last_order\nFROM orders;"},{"t":"p","runs":[{"text":"A customer’s first order gets NULL, every later one gets the gap. "},{"text":"avg(days_since_last_order)","code":true},{"text":" over that result is the average reorder interval, and "},{"text":"lead()","code":true},{"text":" in the same shape gives “days until the next order”, which is what you compute when the question is whether a customer has gone quiet compared with their own habit."}]},{"t":"h2","id":"the-gap-is-in-rows-not-in-time","runs":[{"text":"The gap is in rows, not in time"}]},{"t":"p","runs":[{"text":"lag()","code":true},{"text":" looks back one "},{"text":"row","i":true},{"text":". If a customer has months with no orders, the previous row is not the previous month; it is whenever they last ordered. For “revenue in the previous calendar month” on a table with gaps, either join to a spine first so that every month exists as a row, or look the value up by date with a self-join on "},{"text":"month - interval '1 month'","code":true},{"text":". The window over a spine is cleaner and is the usual answer. The mistake to avoid is computing month-over-month change with "},{"text":"lag()","code":true},{"text":" on a table that skips a month, which reports a two-month change as if it were one."}]},{"t":"h2","id":"firstvalue-lastvalue-and-a-trap","runs":[{"text":"first_value, last_value, and a trap"}]},{"t":"p","runs":[{"text":"first_value(price) OVER (PARTITION BY product_id ORDER BY changed_at)","code":true},{"text":" gives every row the product’s original price. "},{"text":"last_value()","code":true},{"text":" should, by symmetry, give the latest, and it does not:"}]},{"t":"code","lang":"sql","label":"SQL","text":"last_value(price) OVER (PARTITION BY product_id ORDER BY changed_at)\n-- returns each row's OWN price"},{"t":"p","runs":[{"text":"The reason is the default frame from the previous lesson. It runs from the start of the partition to the current row, so the “last” value in the frame is always the current one. To see the genuine last value the frame has to extend to the end:"}]},{"t":"code","lang":"sql","label":"SQL","text":"last_value(price) OVER (PARTITION BY product_id ORDER BY changed_at\n                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)"},{"t":"p","runs":[{"text":"This is the most searched-for window function bug there is, and it is the frame every time. "},{"text":"nth_value(price, 2)","code":true},{"text":" has the same behaviour and the same fix."}]},{"t":"h2","id":"direction-and-streaks","runs":[{"text":"Direction and streaks"}]},{"t":"p","runs":[{"text":"Comparing a row with its predecessor is how you classify movement: "},{"text":"CASE WHEN price > lag(price) OVER w THEN 'up' WHEN price < lag(price) OVER w THEN 'down' ELSE 'flat' END","code":true},{"text":". Count the ‘up’ rows and you have how often the price rose. Flag the rows where the direction changed and take a running sum of the flags, and you have numbered the runs, which is the next lesson’s method for streaks and sessions."}]},{"t":"h2","id":"what-lag-cannot-do","runs":[{"text":"What lag cannot do"}]},{"t":"p","runs":[{"text":"It looks back a fixed number of rows in one ordering. “The previous order from the same branch” when the window is partitioned by customer needs a different partition and therefore a second window, and “the previous order over ₹1,000” needs a filter before the window, because "},{"text":"lag()","code":true},{"text":" cannot skip rows conditionally. A "},{"text":"lag()","code":true},{"text":" on a filtered CTE handles the second case; the first is two windows in one query, which the first lesson of this module showed is fine."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Every "},{"text":"lag()","code":true},{"text":" answers two questions you should be able to state: ordered by what, and partitioned by what. And any "},{"text":"last_value()","code":true},{"text":" without an explicit frame is a bug until proved otherwise."}]}],"check":{"question":[{"text":"last_value(status) OVER (PARTITION BY ticket_id ORDER BY changed_at)","code":true},{"text":" is meant to show every row of a ticket’s history alongside the ticket’s final status, but each row shows its own status instead. What fixes it?"}],"options":[{"text":[{"text":"Replace "},{"text":"last_value","code":true},{"text":" with "},{"text":"first_value","code":true},{"text":" and reverse the ORDER BY to "},{"text":"changed_at DESC","code":true},{"text":", because last_value is only reliable on the first row of a partition."}],"correct":false,"tempting":[{"text":"Reversing the order makes first_value return the final status, which works, but for the wrong stated reason; last_value is reliable once its frame reaches the end of the partition."}]},{"text":[{"text":"Add "},{"text":"ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING","code":true},{"text":", because the default frame ends at the current row."}],"correct":true,"tempting":null},{"text":[{"text":"Remove the ORDER BY, because last_value only looks at the whole partition when the rows are unordered."}],"correct":false,"tempting":[{"text":"Without ORDER BY the frame is the whole partition but “last” is then undefined, so the value returned is arbitrary."}]},{"text":[{"text":"Use "},{"text":"max(changed_at)","code":true},{"text":" in a GROUP BY and join back, since window functions cannot see rows after the current one."}],"correct":false,"tempting":[{"text":"Windows can see following rows whenever the frame includes them; the join-back works but is a workaround for a one-clause fix."}]}],"order":[2,1,3,0],"why":null},"prev":{"slug":"running-totals-and-frames","title":"Running totals, and the frame you did not know you set","minutes":9},"next":{"slug":"moving-averages","title":"Moving averages: seven rows is not seven days","minutes":8}}}