Question

2173. Longest Winning Streak

中文文档

Description

Table: Matches

+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id   | int  |
| match_day   | date |
| result    | enum |
+-------------+------+
(player_id, match_day) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the ID of a player, the day of the match they played, and the result of that match.
The result column is an ENUM (category) type of ('Win', 'Draw', 'Lose').

 

The winning streak of a player is the number of consecutive wins uninterrupted by draws or losses.

Write a solution to count the longest winning streak for each player.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Matches 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-08 | Win  |
| 2     | 2022-02-06 | Lose   |
| 2     | 2022-02-08 | Lose   |
| 3     | 2022-03-30 | Win  |
+-----------+------------+--------+
Output: 
+-----------+----------------+
| player_id | longest_streak |
+-----------+----------------+
| 1     | 3        |
| 2     | 0        |
| 3     | 1        |
+-----------+----------------+
Explanation: 
Player 1:
From 2022-01-17 to 2022-01-25, player 1 won 3 consecutive matches.
On 2022-01-31, player 1 had a draw.
On 2022-02-08, player 1 won a match.
The longest winning streak was 3 matches.

Player 2:
From 2022-02-06 to 2022-02-08, player 2 lost 2 consecutive matches.
The longest winning streak was 0 matches.

Player 3:
On 2022-03-30, player 3 won a match.
The longest winning streak was 1 match.

 

Follow up: If we are interested in calculating the longest streak without losing (i.e., win or draw), how will your solution change?

Solutions

Solution 1

# Write your MySQL query statement below
WITH
  S AS (
    SELECT
      *,
      ROW_NUMBER() OVER (
        PARTITION BY player_id
        ORDER BY match_day
      ) - ROW_NUMBER() OVER (
        PARTITION BY player_id, result
        ORDER BY match_day
      ) AS rk
    FROM Matches
  ),
  T AS (
    SELECT player_id, SUM(result = 'Win') AS s
    FROM S
    GROUP BY player_id, rk
  )
SELECT player_id, MAX(s) AS longest_streak
FROM T
GROUP BY player_id;