Longest Winning Streak of Each Football Club — SQL Problem
A club's winning streak is a run of consecutive wins in its own matches taken in date order; a draw or a defeat ends it.
- Difficulty: Hard
- Topics: Window Functions
- Dialect: MySQL
- Problem: #466
Problem statement
A club's winning streak is a run of consecutive wins in its own matches taken in date order; a draw or a defeat ends it. A club plays at most one match on any date.
Return every club that played at least one match, with the columns club and longest_win_streak (the length of its longest streak, 0 if it never won). Order the rows by longest_win_streak descending, then by club.
Tables
Table: ClubMatch
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| home_club | varchar |
| away_club | varchar |
| home_goals | int |
| away_goals | int |
Primary key: match_id.
One row per match played; goals are never NULL.
Examples
Example 1
ClubMatch
| match_id | match_date | home_club | away_club | home_goals | away_goals |
|---|---|---|---|---|---|
| 1 | 2024-09-01 | Goa Gulls | Pune Rovers | 2 | 0 |
| 2 | 2024-09-08 | Pune Rovers | Delhi Falcons | 1 | 1 |
| 3 | 2024-09-15 | Delhi Falcons | Goa Gulls | 0 | 1 |
| 4 | 2024-09-22 | Goa Gulls | Delhi Falcons | 3 | 2 |
| 5 | 2024-09-29 | Pune Rovers | Goa Gulls | 2 | 1 |
| 6 | 2024-10-06 | Goa Gulls | Pune Rovers | 4 | 0 |
| 7 | 2024-10-13 | Delhi Falcons | Pune Rovers | 2 | 1 |
| 8 | 2024-10-20 | Delhi Falcons | Goa Gulls | 1 | 0 |
Output
| club | longest_win_streak |
|---|---|
| Goa Gulls | 3 |
| Delhi Falcons | 2 |
| Pune Rovers | 1 |
How to solve Longest Winning Streak of Each Football Club
First unpivot each match into two rows, one per club, with a flag won (draws and defeats are 0). Now each club has a sequence of results ordered by date — guaranteed to be a strict order, since a club plays at most once per date.
Consecutive wins are islands in that sequence. The classic trick numbers each club's matches twice: ROW_NUMBER() over all its matches, and ROW_NUMBER() over just its wins (partition by club and won). Along a run of consecutive wins both numbers grow by one per row, so their difference is constant; any non-win between two wins bumps the first number but not the second, giving the next island a different difference. Grouping the win rows by (club, difference) and counting gives each streak's length; the longest is MAX.
A club that never won has no win rows, so the solution starts from the list of clubs and LEFT JOINs the streaks, with COALESCE(..., 0). The alternative labels each row with the running number of non-wins so far — constant through a winning run — and sums won per label; a club with no wins gets runs of 0 automatically. Both are a couple of sorts over the unpivoted rows.
Reference solution (MySQL)
WITH side AS (
SELECT home_club AS club, match_date, CASE WHEN home_goals > away_goals THEN 1 ELSE 0 END AS won FROM ClubMatch
UNION ALL
SELECT away_club, match_date, CASE WHEN away_goals > home_goals THEN 1 ELSE 0 END FROM ClubMatch
), numbered AS (
SELECT club, won,
ROW_NUMBER() OVER (PARTITION BY club ORDER BY match_date)
- ROW_NUMBER() OVER (PARTITION BY club, won ORDER BY match_date) AS island
FROM side
), streaks AS (
SELECT club, island, COUNT(*) AS len FROM numbered WHERE won = 1 GROUP BY club, island
)
SELECT c.club, COALESCE(MAX(s.len), 0) AS longest_win_streak
FROM (SELECT DISTINCT club FROM side) c
LEFT JOIN streaks s ON s.club = c.club
GROUP BY c.club
ORDER BY longest_win_streak DESC, c.clubAnother way
WITH side AS (
SELECT home_club AS club, match_date, IF(home_goals > away_goals, 1, 0) AS won FROM ClubMatch
UNION ALL
SELECT away_club, match_date, IF(away_goals > home_goals, 1, 0) FROM ClubMatch
), grouped AS (
SELECT club, won,
SUM(1 - won) OVER (PARTITION BY club ORDER BY match_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS breaks
FROM side
)
SELECT club, MAX(run) AS longest_win_streak
FROM (SELECT club, breaks, SUM(won) AS run FROM grouped GROUP BY club, breaks) r
GROUP BY club
ORDER BY longest_win_streak DESC, club← Goalkeepers' Clean-Sheet Percentage · Net Run Rate of Every Team in the T20 League →