Longest Winning Streak — LeetCode 2173 Python Solution

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.

+-------------+------+
| 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').

import duckdb
import pandas as pd

def solution(matches: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Matches", matches)
    return con.execute("""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;""").df()