The Longest Winning Streak interview question is a Database/SQL problem. You are provided with a table containing match results (e.g., player_id, match_date, and result which can be 'Win', 'Loss', or 'Draw'). Your task is to calculate the longest consecutive sequence of 'Win' results for each player across all their matches, ordered chronologically.
This is a classic "Gaps and Islands" SQL problem. Companies that rely heavily on data analytics (like Amazon and Bloomberg) ask this to assess your mastery of Window Functions. Finding contiguous sequences in relational databases is notoriously tricky without loops or procedural code, so this problem perfectly tests whether a candidate can leverage advanced SQL features like ROW_NUMBER() and aggregations to group sequential data.
This problem is solved using the Gaps and Islands pattern via SQL Window Functions. The trick is to generate two distinct row numbers for each player, ordered by date: one for all matches, and one specifically for the 'Win' matches. By subtracting the 'Win' row number from the overall row number, you generate a constant "island ID" for continuous streaks of wins. You then group by the player and this island ID, count the wins, and find the maximum count per player.
Imagine player 101's matches:
Let's apply the math:
| Result | Overall_Row_Num | Win_Row_Num | Difference (Island ID) |
|---|---|---|---|
| Win | 1 | 1 | 0 |
| Win | 2 | 2 | 0 |
| Loss | 3 | (null) | - |
| Win | 4 | 3 | 1 |
Notice how the first two consecutive wins share the same Difference ID (0). The third win, separated by a loss, gets a different ID (1). We can now safely GROUP BY player_id, Difference and COUNT(*) the wins. The maximum count for player 101 is 2.
A very frequent mistake is attempting to use self-joins or correlated subqueries to check if the "previous day" was also a win. This breaks entirely if matches happen on the same day or if there are unpredictable gaps between match dates. Another common error is failing to return 0 for players who have played matches but never actually recorded a single 'Win'.
To master the Longest Winning Streak SQL pattern, you must memorize the "Difference of Row Numbers" technique. It is the gold standard for solving contiguous streak problems in SQL. Practice writing ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) fluently, and ensure you understand how subtracting two monotonically increasing sequences isolates contiguous data blocks.
