A
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.
LAG returns a value from a prior row in the same window partition. It is commonly used to compare the current row to the previous day, month, or event without joining the table to itself.
LAG(revenue) OVER (PARTITION BY region ORDER BY month_start)
LEAD returns a value from a following row in the same window partition. It is useful for forward-looking comparisons, such as checking the next period's revenue or identifying upcoming changes.
LEAD(revenue) OVER (PARTITION BY region ORDER BY month_start)
PARTITION BY defines independent sequences, such as one sequence per customer or region. ORDER BY defines the row sequence within each partition, which is critical because LAG and LEAD operate relative to that order.
LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month_start)
Once the previous or next value is available, you can compute absolute change and percentage change with simple arithmetic. You usually need CASE or NULLIF to avoid division-by-zero errors when calculating percentages.
revenue - LAG(revenue) OVER (ORDER BY month_start)
The first row in a partition has no previous row, so LAG returns NULL unless a default is supplied. Missing calendar periods can also produce misleading comparisons if the data is not first aggregated to a complete time series.
LAG(revenue, 1, 0) OVER (ORDER BY month_start)