How do LAG and LEAD functions work in SQL?
This question assesses your ability to compare current rows with previous or next rows. It evaluates proficiency in time-series analysis and trend detection.
Why Interviewers Ask This
Analyzing trends over time is a core data task. Interviewers ask this to see if you can avoid self-joins for simple row comparisons. LAG and LEAD are efficient tools for calculating deltas or detecting patterns in sequential data.
How to Answer This Question
Explain that LAG retrieves data from a preceding row and LEAD from a succeeding row within a partition. Mention they are window functions that require an ORDER BY clause. Give examples like comparing today's sales to yesterday's or predicting next month's revenue.
Key Points to Cover
- LAG accesses previous row
- LEAD accesses next row
- Avoids self-joins for comparison
Sample Answer
LAG and LEAD are window functions that allow access to data from previous or next rows without self-joining. LAG retrieves the value from the row prior to the current one, while LEAD gets the value from the next row. I u…
Common Mistakes to Avoid
- Forgetting to partition and order the window
- Using them without a clear time dimension
- Confusing LAG and LEAD direction
Sound confident on this question in 5 minutes
Answer once and get a 30-second AI critique of your structure, content, and delivery. First attempt is free — no signup needed.