SQL interview question: Consecutive Increases (Gaps and Islands)
Detect Consecutive Price Increases
Trading analysts want to identify the longest streak of consecutive daily price increases.
Find the longest run of days where closing_price increased from the previous day.
Return streak_start, streak_end, streak_length, and price_gain (end price - start price). Show the single longest streak.
Skills: advanced gaps and islands with LEAD/LAG
Tables
stock_prices 10 rows
- price_date text PK
- closing_price real
| price_date | closing_price |
|---|---|
| 2025-01-01 | 150.0 |
| 2025-01-02 | 152.5 |
| 2025-01-03 | 148.75 |