Winning Streaks in MySQL

-- ---------------------------------------------------------------------------
-- Winning Streaks
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=803
-- ---------------------------------------------------------------------------

-- 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;