Explain LAG and LEAD functions
This tests accessing previous or next row values. It is essential for trend analysis.
Why Interviewers Ask This
Comparing current values to historical ones is common. Interviewers ask this to verify knowledge of window functions that avoid self-joins. It shows efficiency in time-series analysis.
How to Answer This Question
Define LAG as accessing previous row values and LEAD as accessing next row values. Explain they work within a partition ordered by time. Mention use cases like detecting trends or calculating period-over-period changes.
Key Points to Cover
- LAG gets previous row value
- LEAD gets next row value
- Works within partitions
- Avoids self-joins for history
Sample Answer
LAG and LEAD are window functions that let you look at values from previous or next rows in the same result set. LAG accesses the prior row, while LEAD accesses the subsequent row. They are used for comparisons across ro…
Common Mistakes to Avoid
- Using offsets incorrectly
- Forgetting ORDER BY clause
- Not handling first/last row NULLs
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.