Player Peak Win Run

Hard

A competitive gaming analytics team tracks every player's match outcomes and needs a robust measure of momentum. Table: Matches player_id: unique identifier of the player match_day: calendar day of the match result: match outcome with values "Win", "Draw", or "Lose" Data guarantees: (player_id, match_day) is unique For each player, chronological order is determined by match_day Definition: A winning streak is the number of consecutive matches with result = "Win" for one player, uninterrupted by "Draw" or "Lose" rows. Task: For every player present in Matches, return the longest winning streak. Players who never win must have longest_streak = 0. Output requirements: Return exactly two columns: player_id longest_streak Return one row per player in Matches Result may be returned in any order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Matches:
| 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-08  | Win    |
| 2         | 2022-02-06  | Lose   |
| 2         | 2022-02-08  | Lose   |
| 3         | 2022-03-30  | Win    |

Output

[
  {"player_id":1,"longest_streak":3},
  {"player_id":2,"longest_streak":0},
  {"player_id":3,"longest_streak":1}
]

Explanation

Player 1 has a best run of three wins before a draw resets the streak. Player 2 never wins, so the best streak is 0. Player 3 wins once, so the best streak is 1.

Example 2

Input

Matches:
| player_id | match_day   | result |
|-----------|-------------|--------|
| 9         | 2023-04-01  | Win    |
| 9         | 2023-04-15  | Win    |
| 9         | 2023-05-20  | Win    |
| 10        | 2023-07-01  | Draw   |
| 10        | 2023-07-02  | Lose   |
| 11        | 2023-08-10  | Win    |
| 11        | 2023-08-11  | Lose   |
| 11        | 2023-08-12  | Win    |

Output

[
  {"player_id":9,"longest_streak":3},
  {"player_id":10,"longest_streak":0},
  {"player_id":11,"longest_streak":1}
]

Explanation

Large date gaps do not break streaks by themselves. Only non-win outcomes break a winning run.

Example 3

Input

Matches:
| player_id | match_day   | result |
|-----------|-------------|--------|
| 21        | 2024-01-01  | Draw   |
| 21        | 2024-01-02  | Win    |
| 21        | 2024-01-03  | Win    |
| 21        | 2024-01-04  | Lose   |
| 21        | 2024-01-05  | Win    |
| 22        | 2024-01-10  | Draw   |
| 22        | 2024-01-12  | Draw   |
| 22        | 2024-01-14  | Lose   |

Output

[
  {"player_id":21,"longest_streak":2},
  {"player_id":22,"longest_streak":0}
]

Explanation

Player 21 has runs of lengths 2 and 1, so longest is 2. Player 22 has no wins.

Constraints

1 <= number of rows in Matches <= 800000
1 <= player_id <= 1000000000
match_day is a valid YYYY-MM-DD date
result is one of: Win, Draw, Lose
(player_id, match_day) is unique
For each player, evaluate streaks in ascending match_day order
A Draw or Lose row resets the current winning streak
Return exactly two columns: player_id, longest_streak
Return one output row per player present in Matches
If a player has no Win rows, longest_streak must be 0
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exact columns: player_id, longest_streak

Matches: | 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-08 | Win | | 2 | 2022-02-06 | Lose | | 2 | 2022-02-08 | Lose | | 3 | 2022-03-30 | Win |

Matches: | player_id | match_day | result | |-----------|-------------|--------| | 9 | 2023-04-01 | Win | | 9 | 2023-04-15 | Win | | 9 | 2023-05-20 | Win | | 10 | 2023-07-01 | Draw | | 10 | 2023-07-02 | Lose | | 11 | 2023-08-10 | Win | | 11 | 2023-08-11 | Lose | | 11 | 2023-08-12 | Win |

Matches: | player_id | match_day | result | |-----------|-------------|--------| | 21 | 2024-01-01 | Draw | | 21 | 2024-01-02 | Win | | 21 | 2024-01-03 | Win | | 21 | 2024-01-04 | Lose | | 21 | 2024-01-05 | Win | | 22 | 2024-01-10 | Draw | | 22 | 2024-01-12 | Draw | | 22 | 2024-01-14 | Lose |