Longest Winning Streak - Practice Coding Problems

Longest Winning Streak - Problem

Longest Winning Streak - Find the maximum consecutive wins for each player

You are given a table Matches that tracks player match results over time. Each row contains a player's ID, the date they played, and whether they won, lost, or drew that match.

Goal: Calculate the longest winning streak for each player. A winning streak is the number of consecutive wins uninterrupted by any draws or losses.

Table Schema:

+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id   | int  |
| match_day   | date |
| result      | enum |
+-------------+------+

The result column contains values: 'Win', 'Draw', or 'Lose'

Return: A table with player_id and their longest_streak (maximum consecutive wins).

Input & Output

example_1.sql — Basic Example

$ Input: Matches table: +----------+------------+--------+ | player_id| match_day | result | +----------+------------+--------+ | 1 | 2022-01-01 | Win | | 1 | 2022-01-02 | Win | | 1 | 2022-01-03 | Lose | | 1 | 2022-01-04 | Win | | 2 | 2022-01-01 | Win | | 2 | 2022-01-02 | Draw | | 2 | 2022-01-03 | Win | +----------+------------+--------+

› Output: +----------+---------------+ | player_id| longest_streak| +----------+---------------+ | 1 | 2 | | 2 | 1 | +----------+---------------+

💡 Note: Player 1 has two consecutive wins (Jan 1-2), then loses, then wins once. Player 2 wins once, draws (breaking streak), then wins once. Maximum streaks are 2 and 1 respectively.

example_2.sql — No Wins

$ Input: Matches table: +----------+------------+--------+ | player_id| match_day | result | +----------+------------+--------+ | 3 | 2022-01-01 | Lose | | 3 | 2022-01-02 | Draw | | 3 | 2022-01-03 | Lose | +----------+------------+--------+

› Output: +----------+---------------+ | player_id| longest_streak| +----------+---------------+ | 3 | 0 | +----------+---------------+

💡 Note: Player 3 never wins any matches, so their longest winning streak is 0.

example_3.sql — Perfect Streak

$ Input: Matches table: +----------+------------+--------+ | player_id| match_day | result | +----------+------------+--------+ | 4 | 2022-01-01 | Win | | 4 | 2022-01-02 | Win | | 4 | 2022-01-03 | Win | | 4 | 2022-01-04 | Win | +----------+------------+--------+

› Output: +----------+---------------+ | player_id| longest_streak| +----------+---------------+ | 4 | 4 | +----------+---------------+

💡 Note: Player 4 wins all matches consecutively, so their longest winning streak equals their total number of matches (4).

Visualization

Tap to expand

Understanding the Visualization

Scan Match History

Go through each player's matches in chronological order

Detect Streak Breaks

Use LAG() to see if previous match result differs from current

Group Consecutive Wins

Assign same group ID to consecutive wins using running sum

Count Group Sizes

Count wins in each group to get streak lengths

Find Maximum

Get the longest streak for each player

Key Takeaway

🎯 Key Insight: Window functions allow us to detect pattern breaks and group consecutive sequences efficiently. The LAG() function is crucial for identifying when streaks start or end, making this a classic application of SQL analytics functions.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

One pass through data with sorting for window functions

⚡ Linearithmic

Space Complexity

O(n)

Storage for window function calculations and final results

⚡ Linearithmic Space

Constraints

1 ≤ number of players ≤ 10⁴
1 ≤ matches per player ≤ 1000
All match_day values are valid dates
result column contains only 'Win', 'Draw', or 'Lose'
Each (player_id, match_day) combination is unique

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28 N Netflix 22

The optimal solution uses window functions to identify consecutive winning streaks in a single pass. Key technique: LAG() detects streak boundaries, SUM() creates group IDs for consecutive wins, and ROW_NUMBER() counts positions within each streak. Finally, MAX() finds the longest streak per player. Time complexity: O(n log n) due to sorting for window functions.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass Window Function	O(n log n)	O(n)	First pass creates streak groups, second pass finds longest streak
One-Pass Window Function (Optimal)	O(n log n)	O(n)	Calculate longest streaks in a single pass using advanced window functions
Brute Force (Nested Subqueries)	O(n³)	O(1)	Check every possible starting match and count consecutive wins

Two-Pass Window Function — Algorithm Steps

First pass: Use ROW_NUMBER() and LAG() to identify streak breaks
Assign group numbers to consecutive win sequences
Second pass: Count wins in each group
Find maximum group size per player

Visualization

Tap to expand

Step-by-Step Walkthrough

Identify Breaks

Mark where streaks start and end using LAG()

Assign Groups

Give each consecutive win sequence a group number

Count Groups

Count wins in each group and find maximum

Code -

solution.c — C

// C implementation for query generation
#include <stdio.h>

void printTwoPassQuery() {
    printf("WITH streak_groups AS (\n");
    printf("    SELECT player_id, match_day, result,\n");
    printf("    SUM(CASE WHEN result = 'Win' AND\n");
    printf("        (LAG(result) OVER (PARTITION BY player_id ORDER BY match_day) != 'Win'\n");
    printf("         OR LAG(result) OVER (PARTITION BY player_id ORDER BY match_day) IS NULL)\n");
    printf("        THEN 1 ELSE 0 END)\n");
    printf("        OVER (PARTITION BY player_id ORDER BY match_day) as streak_group\n");
    printf("    FROM Matches\n");
    printf("),\n");
    printf("streak_counts AS (\n");
    printf("    SELECT player_id, COUNT(*) as streak_length\n");
    printf("    FROM streak_groups WHERE result = 'Win'\n");
    printf("    GROUP BY player_id, streak_group\n");
    printf(")\n");
    printf("SELECT player_id, MAX(streak_length) as longest_streak\n");
    printf("FROM streak_counts GROUP BY player_id\n");
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window functions plus two passes through data

⚡ Linearithmic

Space Complexity

O(n)

Storage for intermediate results and group assignments

⚡ Linearithmic Space

Constraints

1 ≤ number of players ≤ 10⁴
1 ≤ matches per player ≤ 1000
All match_day values are valid dates
result column contains only 'Win', 'Draw', or 'Lose'
Each (player_id, match_day) combination is unique

43.6K Views

High Frequency

~25 min Avg. Time

1.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Two-Pass Window Function — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler