Hot streaks: each club's longest run of wins
A broadcaster wants a graphic on momentum: for every club, the longest run of consecutive league wins in 2025-26 (home and away games together, in date order). Each club plays exactly once per matchweek, so match_date is unique per club. A draw or a defeat breaks a run. Clubs that never won a game have no streak and must not appear.
If a club has two runs of the same longest length, report the earlier one (the one with the smaller start date).
Task: return club (name), longest_win_streak (number of games), start_date and end_date (dates of the first and last win of that run). Sort by longest_win_streak from high to low, then start_date ascending, then club A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| club_id INTEGER | name TEXT | city TEXT | stadium_capacity INTEGER |
|---|---|---|---|
| 1 | Harbor City FC | Harbor City | 48000 |
| 2 | Rivermoor United | Rivermoor | 36000 |
| 3 | Atlas Athletic | Port Atlas | 41000 |
| 4 | Solent Albion | Solent Bay | 29000 |
| 5 | Kestrel Town | Kestrel | 22000 |
| 6 | Ironbridge Wanderers | Ironbridge | 33000 |
Showing the first 6 of 10 rows.
| goal_id INTEGER | match_id INTEGER | player_id INTEGER | club_id INTEGER | minute INTEGER |
|---|---|---|---|---|
| 1 | 1 | 2 | 1 | 53 |
| 2 | 1 | 5 | 1 | 66 |
| 3 | 2 | 62 | 9 | 32 |
| 4 | 2 | 14 | 2 | 77 |
| 5 | 3 | 21 | 3 | 59 |
| 6 | 3 | 21 | 3 | 65 |
Showing the first 6 of 222 rows.
| match_id INTEGER | season TEXT | matchweek INTEGER | match_date TEXT | home_club_id INTEGER | away_club_id INTEGER | home_goals INTEGER | away_goals INTEGER |
|---|---|---|---|---|---|---|---|
| 1 | 2025-26 | 1 | 2025-08-09 | 1 | 10 | 2 | 0 |
| 2 | 2025-26 | 1 | 2025-08-09 | 2 | 9 | 1 | 1 |
| 3 | 2025-26 | 1 | 2025-08-09 | 3 | 8 | 2 | 2 |
| 4 | 2025-26 | 1 | 2025-08-09 | 4 | 7 | 2 | 3 |
| 5 | 2025-26 | 1 | 2025-08-09 | 5 | 6 | 3 | 1 |
| 6 | 2025-26 | 2 | 2025-08-23 | 9 | 1 | 0 | 0 |
Showing the first 6 of 90 rows.
| player_id INTEGER | name TEXT | club_id INTEGER | position TEXT | nationality TEXT | birth_date TEXT |
|---|---|---|---|---|---|
| 1 | Oliver Hughes | 1 | GK | United Kingdom | 1996-02-16 |
| 2 | Mateusz Nowak | 1 | DF | Poland | 2003-12-02 |
| 3 | Caleb Brooks | 1 | DF | United States | 2000-06-16 |
| 4 | Nour Salem | 1 | MF | Egypt | 2006-02-25 |
| 5 | Kofi Owusu | 1 | MF | Ghana | 1994-08-02 |
| 6 | Ethan Hayes | 8 | FW | United States | 1995-10-27 |
Showing the first 6 of 70 rows.
| transfer_id INTEGER | player_id INTEGER | from_club TEXT | to_club_id INTEGER | transfer_date TEXT | fee_musd REAL |
|---|---|---|---|---|---|
| 1 | 2 | Dunmore Athletic | 1 | 2024-10-01 | 2.5 |
| 2 | 5 | Santa Rosa SC | 1 | 2024-11-05 | 8 |
| 3 | 8 | FC Lindenberg | 2 | 2025-07-11 | 18 |
| 4 | 11 | Real Costa Verde | 2 | 2025-04-18 | 0 |
| 5 | 14 | Nordhavn IF | 2 | 2025-02-07 | 4 |
| 6 | 17 | Olympia Kallithea | 3 | 2024-09-10 | 15 |
Showing the first 6 of 26 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Gaps and islands: subtract the row number within wins from the row number overall. Consecutive wins share the same difference.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.