You are given a table of tennis players and their matches that they could either win (W) or lose (L). Find the longest streak of wins. A streak is a set of consecutive won matches of one player. The streak ends once a player loses their next match. Output the ID of the player or players and the length of the streak.

You are given a table of tennis players and their matches that they could either win (W) or lose (L). Find the longest streak of wins. A streak is a set of consecutive won matches of one player. The streak ends once a player loses their next match. Output the ID of the player or players and the length of the streak.

Schema and Dataset 

CREATE TABLE players_results (match_date DATETIME, match_result VARCHAR(1), player_id BIGINT);

 INSERT INTO players_results (match_date, match_result, player_id) VALUES ('2023-01-01', 'W', 1), ('2023-01-02', 'W', 1), ('2023-01-03', 'L', 1), ('2023-01-04', 'W', 1), ('2023-01-01', 'L', 2), ('2023-01-02', 'W', 2), ('2023-01-03', 'W', 2), ('2023-01-04', 'W', 2), ('2023-01-05', 'L', 2), ('2023-01-01', 'W', 3), ('2023-01-02', 'W', 3), ('2023-01-03', 'W', 3), ('2023-01-04', 'W', 3), ('2023-01-05', 'L', 3);

Answer

WITH ranked_matches AS (

    SELECT 

        player_id,

        match_date,

        match_result,

        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_date) AS match_rank

    FROM 

        players_results

),

grouped_streaks AS (

    SELECT

        player_id,

        match_date,

        match_result,

        match_rank - ROW_NUMBER() OVER (PARTITION BY player_id, match_result ORDER BY match_date) AS streak_group

    FROM 

        ranked_matches

    WHERE 

        match_result = 'W'

),

streak_lengths AS (

    SELECT

        player_id,

        COUNT(*) AS streak_length,

        streak_group

    FROM

        grouped_streaks

    GROUP BY

        player_id, streak_group

),

max_streaks AS (

    SELECT

        player_id,

        MAX(streak_length) AS max_streak

    FROM

        streak_lengths

    GROUP BY

        player_id

),

longest_streak AS (

    SELECT

        player_id,

        max_streak

    FROM

        max_streaks

    WHERE

        max_streak = (SELECT MAX(max_streak) FROM max_streaks)

)

SELECT * FROM longest_streak;

O/p

player_id | max_streak

3              | 4

Explanation:

Player 1: Wins on 2023-01-01 and 2023-01-02 (streak = 2), then loses on 2023-01-03. Another win on 2023-01-04 (streak = 1). Max streak = 2.

Player 2: Wins on 2023-01-02, 2023-01-03, and 2023-01-04 (streak = 3), then loses on 2023-01-05. Max streak = 3.

Player 3: Wins on 2023-01-01, 2023-01-02, 2023-01-03, and 2023-01-04 (streak = 4), then loses on 2023-01-05. Max streak = 4.

Player 3 has the longest streak of 4 consecutive wins.