Longest Winning Streak - Practice Coding Problems

Longest Winning Streak - Problem

Database Database Window Functions Grouping & Aggregation Conditional Logic Hash Table Sliding Window Dynamic Programming Array manipulation Hard

You have a table Matches that contains information about players and their match results:

player_id: The ID of the player
match_day: The date when the match was played
result: The result of the match ('Win', 'Draw', 'Lose')

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

Write an SQL query to count the longest winning streak for each player.

Return the result table in any order.

Table Schema

Matches

Column Name	Type	Description
`player_id` PK	int	ID of the player
`match_day` PK	date	Date when the match was played
`result`	enum	Result of the match: 'Win', 'Draw', or 'Lose'

Primary Key: (player_id, match_day)

Note: Each row represents one match result for a player on a specific day

Input & Output

Example 1 — Mixed Results with Multiple Streaks

Input 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-01	Win
2	2022-02-01	Win
2	2022-02-02	Lose
2	2022-02-03	Win

Output:

player_id	longest_winning_streak
1	3
2	1

💡 Note:

Player 1 has wins on Jan 17-18 and Jan 25 (3 consecutive wins), then a draw breaks the streak. Player 2's longest streak is 1 win since the loss on Feb 2 breaks any potential streak.

Example 2 — No Wins

Input Table:

player_id	match_day	result
1	2022-01-17	Draw
1	2022-01-18	Lose
2	2022-02-01	Lose

Output:

player_id	longest_winning_streak
1	0
2	0

💡 Note:

Both players have no wins, so their longest winning streaks are 0.

Constraints

1 ≤ player_id ≤ 10000
match_day is a valid date
result is one of 'Win', 'Draw', or 'Lose'
1 ≤ number of matches ≤ 1000

Visualization

Tap to expand

Understanding the Visualization

Input

Match results ordered by date

Group Streaks

Use ROW_NUMBER to identify consecutive results

Count Wins

Find longest Win streak per player

Key Takeaway

🎯 Key Insight: Use ROW_NUMBER differences to group consecutive same results, then count the longest Win groups

Asked in

F Facebook 28 a Amazon 22 G Google 18

Use window functions with ROW_NUMBER() to create streak groups, then count consecutive wins and find the maximum streak length per player using GROUP BY.

Table Schema

Matches

Column Name	Type	Description
`player_id` PK	int	ID of the player
`match_day` PK	date	Date when the match was played
`result`	enum	Result of the match: 'Win', 'Draw', or 'Lose'

Primary Key: (player_id, match_day)

Note: Each row represents one match result for a player on a specific day

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with Streak Grouping	O(n log n)	O(n)	Use ROW_NUMBER to create streak groups, then count consecutive wins

Window Function with Streak Grouping — Algorithm Steps

Step 1: Order matches by player and date, assign row numbers
Step 2: Create streak groups using ROW_NUMBER differences
Step 3: Count wins in each group and find maximum per player

Visualization

Tap to expand

Step-by-Step Walkthrough

Order

Sort by player and date

Group

Create streak groups using ROW_NUMBER

Count

Find longest Win streak per player

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for window ordering by match_day

⚡ Linearithmic

Space Complexity

O(n)

Window frame storage and intermediate grouping

⚡ Linearithmic Space

34.5K Views

Medium Frequency

~25 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with Streak Grouping — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler