Explain the purpose of LAG and LEAD functions in SQL
This question tests knowledge of accessing previous or next row values without self-joins.
Why Interviewers Ask This
Comparing rows sequentially is common in trend analysis. Interviewers want to see if you know how to access adjacent rows efficiently.
How to Answer This Question
Define LAG as looking at previous rows and LEAD as looking at next rows. Mention they avoid self-joins. Give examples like comparing today's sales to yesterday's.
Key Points to Cover
- Access adjacent rows
- Avoid self-joins
- Trend analysis
- Time-series comparison
Sample Answer
LAG and LEAD are window functions that let you look at values from previous or next rows in the same result set without self-joins. LAG accesses prior rows, while LEAD accesses future rows. They are used for comparisons…
Common Mistakes to Avoid
- Using OFFSET incorrectly
- Forgetting partitioning
- Not handling first/last rows
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.