2173. Longest Winning Streak

JIAKAOBO

LeetCode

感谢赞助!

• ㊗️
• 大家
• offer
• 多多！

Problem

Table: Matches

+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id   | int  |
| match_day   | date |
| result      | enum |
+-------------+------+
(player_id, match_day) is the primary key 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 type of ('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.

The query 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.

Code

WITH base_data AS (
  SELECT
    M1.*,
    ROW_NUMBER() OVER (PARTITION BY M1.player_id ORDER BY M1.match_day) AS match_seq_num
  FROM
    Matches M1
), grps AS (
  SELECT
    BD.*,
    BD.match_seq_num - ROW_NUMBER() OVER (PARTITION BY BD.player_id ORDER BY BD.match_day) AS grp
  FROM
    base_data BD
  WHERE
    BD.result = 'Win'
), consec_counts AS (
  SELECT
    G.player_id,
    COUNT(G.grp) AS consec_count,
    ROW_NUMBER() OVER(PARTITION BY G.player_id ORDER BY COUNT(G.grp) DESC) AS consec_rnk
  FROM
    grps G
  GROUP BY
    G.player_id, G.grp
)
SELECT
  M2.player_id,
  (CASE WHEN CC.player_id IS NULL THEN 0 ELSE CC.consec_count END) AS longest_streak
FROM
  Matches M2
  LEFT JOIN consec_counts CC ON M2.player_id = CC.player_id
WHERE
  CC.consec_rnk = 1 OR CC.player_id IS NULL
GROUP BY
  M2.player_id, longest_streak;