Longest Winning Streak - Problem
You have a table Matches that contains information about players and their match results:
- player_id: The ID of the player
- match_day: The date when the match was played
- result: The result of the match ('Win', 'Draw', 'Lose')
The winning streak of a player is the number of consecutive wins uninterrupted by draws or losses.
Write an SQL query to count the longest winning streak for each player.
Return the result table in any order.
Table Schema
Matches
| Column Name | Type | Description |
|---|---|---|
player_id
PK
|
int | ID of the player |
match_day
PK
|
date | Date when the match was played |
result
|
enum | Result of the match: 'Win', 'Draw', or 'Lose' |
Primary Key: (player_id, match_day)
Note: Each row represents one match result for a player on a specific day
Input & Output
Example 1 — Mixed Results with Multiple Streaks
Input Table:
| player_id | match_day | result |
|---|---|---|
| 1 | 2022-01-17 | Win |
| 1 | 2022-01-18 | Win |
| 1 | 2022-01-25 | Win |
| 1 | 2022-01-31 | Draw |
| 1 | 2022-02-01 | Win |
| 2 | 2022-02-01 | Win |
| 2 | 2022-02-02 | Lose |
| 2 | 2022-02-03 | Win |
Output:
| player_id | longest_winning_streak |
|---|---|
| 1 | 3 |
| 2 | 1 |
💡 Note:
Player 1 has wins on Jan 17-18 and Jan 25 (3 consecutive wins), then a draw breaks the streak. Player 2's longest streak is 1 win since the loss on Feb 2 breaks any potential streak.
Example 2 — No Wins
Input Table:
| player_id | match_day | result |
|---|---|---|
| 1 | 2022-01-17 | Draw |
| 1 | 2022-01-18 | Lose |
| 2 | 2022-02-01 | Lose |
Output:
| player_id | longest_winning_streak |
|---|---|
| 1 | 0 |
| 2 | 0 |
💡 Note:
Both players have no wins, so their longest winning streaks are 0.
Constraints
-
1 ≤ player_id ≤ 10000 -
match_dayis a valid date -
resultis one of'Win','Draw', or'Lose' -
1 ≤ number of matches ≤ 1000
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code