Leet Code : Longest Winning Streak

Table : Matches

(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.

Get Sridurga Sambasivarao’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Return the result table in any order.

CREATE TABLE Matches
(
player_id INT NOT NULL,
match_day DATE NOT NULL,
result VARCHAR(10) NOT NULL,
CONSTRAINT PK_Matches PRIMARY KEY (player_id, match_day)
);

INSERT INTO Matches
(player_id, match_day, result)
VALUES
(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');

Solution :

WITH cte1 AS
(
SELECT
 player_id,
 match_day,
 result,
 ROW_NUMBER() OVER(PARTITION BY player_id ORDER BY match_day) -
 ROW_NUMBER() OVER(PARTITION BY player_id, result ORDER BY match_day) AS streak_group
FROM matches
),
cte2 AS
(
SELECT 
 player_id,
 SUM(CASE WHEN result = 'Win' THEN 1 ELSE 0 END) AS streak_length
FROM cte1
GROUP BY player_id, streak_group
)
SELECT 
 player_id,
 MAX(streak_length) AS longest_streak
FROM cte2
GROUP BY player_id

Output