Lesson 6 of 14 · 26 min
LAG and LEAD: comparing a row with its neighbours
By the end of this lesson you can use LAG and LEAD with PARTITION BY and ORDER BY to compare consecutive rows, compute changes and time gaps, and handle the NULL at the edge of each partition.
So far every window function has looked at a whole set of rows. Offset functions look at exactly one other row:
LAG(expr)returnsexprfrom the previous row of the partition.LEAD(expr)returnsexprfrom the next row.
"Previous" and "next" are defined by the ORDER BY inside OVER, and PARTITION BY makes sure you never look across into another customer's or account's rows. Before window functions, this needed a self-join on row_number = row_number - 1; LAG and LEAD do it in one line.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons