window

LEAD() and LAG(): Accessing Neighboring Rows

Use LAG() to compare each employee's salary with the previous-highest-paid employee.

Overview

LAG(col, n) returns the value of col from the row n rows before the current one in the window order; LEAD(col, n) looks ahead. They let you compute period-over-period differences, gaps, and trends without self-joins.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

LAG(salary) OVER (ORDER BY salary DESC) — the salary of the previously-listed (next-highest-paid) employee.
salary - LAG(salary) — the gap to that predecessor; NULL for the top row (no predecessor).
The window has no PARTITION BY, so the ordering is company-wide.

Default offset is 1

LAG(col) is shorthand for LAG(col, 1). Pass a second argument for farther neighbors, and a third for the default when none exists.

Variations

Show each customer's previous order date:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

NULL on the boundary. The first row's LAG (and last row's LEAD) is NULL — handle with COALESCE if you need a fallback.
Wrong PARTITION BY. Forgetting it mixes customers together; include PARTITION BY customer_id to keep neighbors within the same entity.
Confusing LEAD and LAG. LAG = previous, LEAD = next.