Skip to main content
Talentd

Longest Winning Streak - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

Problem Statement

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.
The result column is an ENUM (category) type of ('Win', 'Draw', 'Lose').

 

The winning streak of a player is the number of consecutive wins uninterrupted by draws or losses.

Write a solution to count the longest winning streak for each player.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Matches table:
+-----------+------------+--------+
| player_id | match_day  | result |
+-----------+------------+--------+
| 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    |
+-----------+------------+--------+
Output: 
+-----------+----------------+
| player_id | longest_streak |
+-----------+----------------+
| 1         | 3              |
| 2         | 0              |
| 3         | 1              |
+-----------+----------------+
Explanation: 
Player 1:
From 2022-01-17 to 2022-01-25, player 1 won 3 consecutive matches.
On 2022-01-31, player 1 had a draw.
On 2022-02-08, player 1 won a match.
The longest winning streak was 3 matches.

Player 2:
From 2022-02-06 to 2022-02-08, player 2 lost 2 consecutive matches.
The longest winning streak was 0 matches.

Player 3:
On 2022-03-30, player 3 won a match.
The longest winning streak was 1 match.

 

Follow up: If we are interested in calculating the longest streak without losing (i.e., win or draw), how will your solution change?

Approach Overview

Problem Overview: The table stores match results for players with a date and result (Win or Lose). The task is to compute the longest consecutive winning streak for every player. A streak ends immediately when a loss appears in the sequence.

Approach 1: Window Function Streak Grouping (O(n log n) time, O(n) space)

Sort matches per player by match_day and use SQL window functions to identify streak segments. The key idea is to assign a running counter that increases every time a loss appears. You can compute this using SUM(CASE WHEN result = 'Lose' THEN 1 ELSE 0 END) OVER (PARTITION BY player_id ORDER BY match_day). This value acts as a streak group identifier. All consecutive wins between two losses share the same group id.

After assigning groups, filter rows where result = 'Win' and count wins inside each group using GROUP BY player_id, group_id. The maximum count per player is the longest winning streak. Window functions make the segmentation clean and avoid procedural logic. This approach is reliable for ordered event streams and commonly used in SQL streak or session problems.

Approach 2: MySQL User Variable Simulation (O(n log n) time, O(1) extra space)

Older MySQL versions without full window function support often rely on user variables. Iterate through rows ordered by player_id and match_day, maintaining two variables: the current streak and the best streak seen so far. When the result is Win, increment the streak; when a Lose appears, reset it to zero. A running MAX() per player tracks the best streak. While slightly harder to read and maintain, it avoids extra grouping logic and still processes rows in a single pass.

This technique behaves like a streaming algorithm inside SQL. It works well for ordered logs but requires careful ordering and variable resets when the player changes. Many production SQL systems historically used this approach before modern window functions became standard.

Recommended for interviews: The window function approach. It demonstrates strong SQL fundamentals, especially how to segment ordered records using analytic functions. Interviewers expect familiarity with streak problems and window-based grouping. The variable approach shows creativity, but the window solution is cleaner, easier to reason about, and aligns with modern database query patterns.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Window Function Streak GroupingO(n log n)O(n)Preferred modern SQL solution when window functions are available
MySQL User Variable SimulationO(n log n)O(1)Useful in older MySQL environments without full analytic function support

Video Solution

Leetcode HARD 2173 - Longest Winning Streak - DOUBLE RANKING DIFFERENCE - Solved & Explained by EDS • Everyday Data Science • 2,024 views views

Frequently Asked Questions

Is Longest Winning Streak easy or hard?
The problem is labeled Hard because it requires understanding streak segmentation in SQL. The logic itself is straightforward once you know the window-function grouping trick, but recognizing that pattern is what makes the challenge difficult.
Longest Winning Streak Python/Java solution
When implemented outside SQL, the problem becomes a simple scan of sorted matches. Use a counter for the current win streak and update a maximum value whenever the player wins. Reset the counter when a loss occurs. Time complexity is O(n) after sorting by player and date.
How to solve Longest Winning Streak in O(n)?
If the data is already sorted by player_id and match_day, the computation effectively becomes O(n). You scan rows sequentially, increase a streak counter for each win, reset it on a loss, and track the maximum streak per player. In SQL this is usually implemented using window functions or user variables.
What is the best approach for Longest Winning Streak?
The window function streak-grouping approach is the most reliable solution. It partitions matches by player, orders them by match_day, and uses a cumulative loss counter to break sequences into groups. Counting wins inside each group and taking the maximum gives the longest streak. This approach runs in O(n log n) time due to sorting.
Is Longest Winning Streak asked at Google/Amazon/Meta?
Streak and session segmentation problems appear frequently in data engineering and analytics interviews at companies like Amazon, Google, and Meta. Variations of this problem test your ability to process ordered events and detect consecutive patterns using SQL window functions.
What data structure is used in Longest Winning Streak?
The problem relies on ordered event processing rather than traditional in-memory data structures. SQL window functions act like a running state machine over sorted rows, tracking cumulative conditions such as losses to split streak groups.
What is the time complexity of Longest Winning Streak?
Most SQL solutions run in O(n log n) time because the matches must be ordered by player and date before applying window functions or sequential logic. The grouping and aggregation operations themselves are linear after sorting. Space complexity is typically O(n) for intermediate results.

Ready to solve this problem?

Practice Longest Winning Streak with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary