SQL Server Interview Question #96

What are LAG() and LEAD()?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Quick Interview Answer

LAG accesses a value from a preceding row in the window, while LEAD accesses a value from a following row. They are useful for comparing adjacent records without writing a self join. Typical uses include comparing current sales with previous-day sales, measuring changes between events, and examining previous or next status values.

Detailed Explanation

LAG accesses a value from a preceding row in the window, while LEAD accesses a value from a following row. They are useful for comparing adjacent records without writing a self join.

Typical uses include comparing current sales with previous-day sales, measuring changes between events, and examining previous or next status values.

Code Example

SELECT OrderDate,
       TotalAmount,
       LAG(TotalAmount) OVER (ORDER BY OrderDate, Id) AS PreviousAmount,
       LEAD(TotalAmount) OVER (ORDER BY OrderDate, Id) AS NextAmount
FROM dbo.Orders;