toby's Journal: Uses for self JOIN: 11) winning streak

Cluber on #mysql wanted to find the longest winning streak in a series of matches, like this:

mysql> select * from m;
+----+--------+-------+
| id | winner | loser |
+----+--------+-------+
| 29 | A | B |
| 30 | A | C |
| 31 | C | B |
| 32 | B | A |
| 33 | A | D |
| 34 | B | C |
| 35 | C | D |
| 36 | E | D |
+----+--------+-------+
8 rows in set (0.00 sec)

Longest winning streak per user is the longest series of wins by a user before they lose a match. The wins need not be consecutive. Here is a SQL solution:

mysql> select Streak.winner, max(Streak.cnt)
from (
select m0.id, m0.winner, count(Game.id) as cnt
from m m0
join (
select m1.id, min(m2.id) as minid
from m m1
left join m m2 on m2.loser = m1.winner
and m2.id > m1.id
group by m1.id
) Loss on Loss.id = m0.id
join m Game on Game.winner = m0.winner
and Game.id >= m0.id
and (Loss.minid is null
or Game.id < Loss.minid)
group by m0.id
) Streak
group by Streak.winner;

+--------+-----------------+
| winner | max(Streak.cnt) |
+--------+-----------------+
| A | 2 |
| B | 2 |
| C | 1 |
| E | 1 |
+--------+-----------------+
4 rows in set (0.01 sec)