How do the LEAD and LAG functions work in SQL?

LEAD and LAG are analytic (window) functions that allow you to access data from subsequent or preceding rows within the same result set without a self‑join. They are defined as LEAD(value_expression, offset, default) OVER (PARTITION BY … ORDER BY …) and similarly for LAG. The offset defaults to 1, meaning the next or previous row. If the target row does not exist, the default value is returned (or NULL if omitted). These functions are useful for calculating differences between rows, creating running totals, or comparing current row values to neighbors. For example, to compute the difference between a sales amount and the previous month’s sales, you can use LAG(sales) OVER (ORDER BY month). They operate row‑by‑row within the defined window, making them efficient for many analytical queries.