/*
Amazon SQL Interview Question

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 Setup
CREATE TABLE players_results ( match_date DATETIME, match_result VARCHAR(1), player_id INT);

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);

-- Solution
WITH ranked_results AS (
    SELECT
        match_date,
        match_result,
        player_id,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_date) -
        ROW_NUMBER() OVER (PARTITION BY player_id, match_result ORDER BY match_date) AS streak_grp
    FROM players_results
),
streaks AS (
    SELECT
        player_id,
        COUNT(streak_grp) AS streak_length,
        RANK() OVER (ORDER BY COUNT(streak_grp) DESC) AS rnk
    FROM ranked_results
    GROUP BY player_id, streak_grp
)

SELECT 
    player_id,
    streak_length AS longest_streak
FROM streaks
WHERE rnk = 1;