SQL interview question: Longest Winning Streak
Longest Winning Streak in Games
Sports analytics needs to find each player's longest consecutive winning streak.
Calculate the maximum number of consecutive wins for each player (gaps and islands problem).
Return player_id and longest_win_streak. Use ROW_NUMBER() minus game_id to identify consecutive groups.
Skills: advanced gaps and islands pattern with streak tracking
Tables
game_results 15 rows
- game_id integer PK
- player_id integer
- game_date text
- result text
| game_id | player_id | game_date | result |
|---|---|---|---|
| 1 | 1 | 2025-01-01 | win |
| 2 | 1 | 2025-01-02 | win |
| 3 | 1 | 2025-01-03 | win |