Longest Winning Streak - Solution & Explanation

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.