Leetcode Problem 2173. Longest Winning Streak

2173. Longest Winning Streak

Leetcode Solutions

Calculating Longest Winning Streak Using Window Functions and CTEs

Core Logic Steps for Code Implementation

  1. Create the RankedMatches CTE:
    • Use ROW_NUMBER() to generate two sequences of row numbers for each player's matches.
    • Create a streak_group identifier by subtracting these sequences.
    • Mark non-winning results with a 1 using a CASE statement.
  2. Create the Streaks CTE:
    • Calculate the cumulative sum of wins within each streak_group using SUM() and a window function.
  3. Main Query:
    • Select the player_id and the maximum streak length for each player.
    • Use a CASE statement to ensure only streaks of wins are considered.
    • Group by player_id to find the longest streak for each player.

erDiagram
    Matches {
        int player_id
        date match_day
        enum result
        player_id match_day PK
    }

Calculating Longest Winning Streak Using Group-By and Self-Join

Ask Question

Programming Language
image/screenshot of info(optional)
Full Screen
Loading...

Suggested Answer

Answer
Full Screen
Copy Answer Code
Loading...