Q18: How do LEAD() and LAG() window functions work?
LEAD() and LAG() are positional window functions used to inspect adjacent row data relative to the current row without performing a self-join.
LAG(col, offset, default): Accesses column values from preceding rows.LEAD(col, offset, default): Accesses column values from subsequent rows.
Calculating Month-over-Month Sales Growth
SELECT
sale_month,
revenue,
LAG(revenue, 1, 0.0) OVER (ORDER BY sale_month) AS prev_month_revenue,
revenue - LAG(revenue, 1, 0.0) OVER (ORDER BY sale_month) AS mom_change
FROM monthly_sales;💡 LAG retrieves the previous month's revenue to compute month-over-month differences directly in the current row.
Key Takeaways
- LAG inspects previous records; LEAD inspects upcoming records.
- They are ideal for calculating month-over-month trends, running totals, and time-series differences.