LAG and LEAD for Adjacent Rows

One of the most common analyst interview prompts is: "Compare each row to the one before it without a self-join." Think month-over-month revenue, a user's previous login, or the next event in a sequence.

The clean answer is the LAG and LEAD window functions. They let a row peek at a neighboring row's value while keeping every detail row intact. In this lesson you will build a precise mental model of how they navigate adjacent rows.

LAG(col) returns the value of col from the previous row. LEAD(col) returns the value from the next row. "Previous" and "next" are defined entirely by the ORDER BY inside the OVER clause.

• LAG looks backward.
• LEAD looks forward.

Both are offset window functions: they never collapse rows, they just attach a neighbor's value to the current row.