Compare Periods with LAG and LEAD

Context

Teams often need to compare this month’s sales, signups, or revenue to the prior or next period without writing complex self-joins. PostgreSQL window functions like LAG and LEAD are designed for this kind of sequential analysis.

Core Question

Explain how you would use LAG and LEAD to compare a current period with the previous or next period in SQL.

Your answer should cover:

1. What LAG and LEAD do
2. How PARTITION BY and ORDER BY affect the result
3. How to calculate period-over-period differences and percentage changes
4. When these functions are better than self-joins
5. Common edge cases such as missing periods or the first row in a sequence

Scope Guidance

The interviewer expects a practical explanation with PostgreSQL examples, not just definitions. Focus on time-based comparisons such as monthly revenue by customer, product, or region, and explain how the window frame is determined logically by row order.