Longest Winning Streak of Each Football Club

Hard
466.

Longest Winning Streak of Each Football Club

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

ColumnType
match_idint
match_datedate
home_clubvarchar
away_clubvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played; goals are never NULL.

Example

The rows must come back in the order shown.

ClubMatch

match_idmatch_datehome_clubaway_clubhome_goalsaway_goals
12024-09-01Goa GullsPune Rovers20
22024-09-08Pune RoversDelhi Falcons11
32024-09-15Delhi FalconsGoa Gulls01
42024-09-22Goa GullsDelhi Falcons32
52024-09-29Pune RoversGoa Gulls21
62024-10-06Goa GullsPune Rovers40
72024-10-13Delhi FalconsPune Rovers21
82024-10-20Delhi FalconsGoa Gulls10

Output

clublongest_win_streak
Goa Gulls3
Delhi Falcons2
Pune Rovers1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
match_datedate
home_clubvarchar
away_clubvarchar
home_goalsint
away_goalsint

Primary key: match_id.

One row per match played; goals are never NULL.

Examples

Example 1

ClubMatch

match_idmatch_datehome_clubaway_clubhome_goalsaway_goals
12024-09-01Goa GullsPune Rovers20
22024-09-08Pune RoversDelhi Falcons11
32024-09-15Delhi FalconsGoa Gulls01
42024-09-22Goa GullsDelhi Falcons32
52024-09-29Pune RoversGoa Gulls21
62024-10-06Goa GullsPune Rovers40
72024-10-13Delhi FalconsPune Rovers21
82024-10-20Delhi FalconsGoa Gulls10

Output

clublongest_win_streak
Goa Gulls3
Delhi Falcons2
Pune Rovers1

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.club

Another 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 →