Winning streaks

from the Artful Common Queries page

Given a table of IDs and won-lost results, how do we find the longest winning streak? 
drop table if exists results;
create table results(id int,result char(1));
insert into results values
(1,'w'),(2,'l'),(3,'l'),(4,'w'),(5,'w'),(6,'w'),(7,'l'),(8,'w'),(9,'w');
select * from results;
+------+--------+
| id   | result |
+------+--------+
|    1 | w      |
|    2 | l      |
|    3 | l      |
|    4 | w      |
|    5 | w      |
|    6 | w      |
|    7 | l      |
|    8 | w      |
|    9 | w      |
+------+--------+
We can find streaks of two with a left join on a.id=b.id+1. To count streak lengths, initialise a counter to 0 then increment it for every hit: 
set @count=0;
select a.id, a.result, b.result, @count := IF(a.result = b.result, @count + 1, 1) as Streak
from results a 
left join results b on a.id = b.id + 1 
where a.result = 'w';
The longest winning streak is the longest such streak found: 
set @count=0;
select MAX(@count:=IF(a.result = b.result, @count + 1, 1)) as LongestStreak
from results a 
left join results b on a.id = b.id + 1 
where a.result = 'w';
+---------------+
| LongestStreak |
+---------------+
|             3 |
+---------------+
That solution is from a response by Jon Roshko to a question by Ed Ball on the MySQL Newbie Forum. Scott Noyes points out that our query pattern for sequence starts and ends also works for winning streaks: 
SELECT MIN( c.id ) - a.id + 1 as LongestStreak
FROM results AS a
LEFT JOIN results AS b ON a.id = b.id + 1 AND b.result = 'w'
LEFT JOIN results AS c ON a.id <= c.id AND c.result = 'w'
LEFT JOIN results AS d ON c.id = d.id - 1 AND d.result = 'w'
WHERE
  a.result = 'w'
  AND b.id IS NULL
  AND c.id IS NOT NULL
  AND d.id IS NULL
GROUP BY a.id
ORDER BY LongestStreak DESC LIMIT 1;

Last updated 22 May 2024