Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**LAG approach**: WITH t AS (SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_rev FROM sales) SELECT date, revenue FROM t WHERE revenue < prev_rev AND prev_rev IS NOT NULL. **Consecutive pairs**: Self-join ON a.date = b.date + 1 WHERE a.revenue < b.revenue. **N...
LAG approach: WITH t AS (SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_rev FROM sales) SELECT date, revenue FROM t WHERE revenue < prev_rev AND prev_rev IS NOT NULL. Consecutive pairs: Self-join ON a.date = b.date + 1 WHERE a.revenue < b.revenue. N consecutive: Extend with multiple LAG or recursive pattern. Gaps: Date sequence must be continuous or use date diff.
Red Flag: Ignoring date gaps (weekends, holidays). Pro-Move: 'We used ROW_NUMBER() OVER (ORDER BY date) - date diff to group consecutive days, then LAG for trend—handled gaps correctly.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.