Intermediate LevelSQL & MySQL
How do LEAD() and LAG() window functions compare adjacent rows?
❓ Interview Prompt: Explain `LAG()` and `LEAD()` analytic functions, offset parameters, default fallbacks, and calculating row-over-row deltas.
The 'LAG()' and 'LEAD()' window functions allow a query to inspect values from preceding or succeeding rows without performing expensive self-joins:
•
'LAG(col, offset, default)': Fetches a column value from 'offset' rows before the current row.
•
'LEAD(col, offset, default)': Fetches a column value from 'offset' rows after the current row.
•
Primary Use Case: Calculating row-over-row growth, time differences between events, or stock price changes.
Code Implementation Example
sql
SELECT
sale_date,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS prev_day_revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS daily_growth
FROM daily_sales;💡Calculating daily revenue growth using the LAG window function.
🎯 Interview Key Takeaway
Use LAG and LEAD to calculate period-over-period deltas and compare adjacent rows efficiently.
#SQL#LAG#LEAD#Window Functions#Intermediate
Back to all SQL & MySQL Questions→