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.
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:
LAG and LEAD doPARTITION BY and ORDER BY affect the resultThe 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.