Table matches(player_id, match_date, result) where result is 'W' or 'L'. Find each player's longest winning streak. Write the query.

Question

Assisting AI · Accepted Answer

You can solve this with window functions by grouping consecutive wins. First, assign a row number per player ordered by date:

```sql
WITH ranked AS (
  SELECT
    player_id,
    match_date,
    result,
    ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_date) AS rn,
    SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END) OVER (PARTITION BY player_id ORDER BY match_date) AS win_cnt
  FROM matches
)
```

For each win row, the difference `rn - win_cnt` is constant for a streak of consecutive wins. Group by that difference to get streak lengths:

```sql
, streaks AS (
  SELECT
    player_id,
    rn - win_cnt AS grp,
    COUNT(*) AS streak_len
  FROM ranked
  WHERE result = 'W'
  GROUP BY player_id, rn - win_cnt
)
```

Finally, pick the maximum streak per player:

```sql
SELECT
  player_id,
  MAX(streak_len) AS longest_streak
FROM streaks
GROUP BY player_id;
```

This runs in O(n) time and uses only window functions, making it efficient even on large tables.

Table matches(player_id, match_date, result) where result is 'W' or 'L'. Find each player's longest winning streak. Write the query.

💡 Model Answer

🎤 Get questions like this answered in real-time