MySQL Winning Streak for every Player

Question

I have a table with winner and loser statistics from a game:

id           winner_id   loser_id 

1            1          2             
2            1          2             
3            3          4             
4            4          3             
5            1          2             
6            2          1             
7            3          4             
8            3          2   
9            3          5             
10           3          6             
11           2          3   
12           3          6             
13           2          3         

I want a result table where i can find the highest winning streak of every player in the game. A streak of a player is broken, when he lost a game (player_id = loser_id). It should look like:

player_id    win_streak

1            3                     
2            2                     
3            4                       
4            1                      
5            0                      
6            0                      

I tried many queries with user defined variables etc. but i can't find a solution. Thanks!

SQL Fiddle : http://sqlfiddle.com/#!9/3da5f/1

Answer 1

Is this the same as Alex's approach; I'm not quite sure, except that it seems to have one distinct advantage.... ;-)

SELECT player_id, MAX(CASE WHEN result = 'winner' THEN running ELSE 0 END) streak 
  FROM 
     ( SELECT *
            , IF(player_id = @prev_player,IF(result=@prev_result,@i:=@i+1,@i:=1),@i:=1) running
            , @prev_result := result 
            , @prev_player:=player_id
         FROM 
            ( SELECT id, 'winner' result, winner_id player_id FROM my_table
               UNION
              SELECT id, 'loser', loser_id FROM my_table
            ) x
            , 
            ( SELECT @i:=1,@prev_result = '',@prev_player:='' ) vars
        ORDER  
           BY x.player_id
            , x.id
     ) a
 GROUP 
    BY player_id;

Answer 2

I guess you should better to do that on php (or any other language you use) side.

But just to give you some idea and as experiment and example for some unique cases (hope it could be useful somewhere)

Here is my approach:

http://sqlfiddle.com/#!9/57cc65/1

SELECT r.winner_id,
  (SELECT MAX(IF(winner_id=r.winner_id,IF(@i IS NULL, @i:=1,@i:=@i+1), IF(loser_id = r.winner_id, @i:=0,0)))
   FROM Results r1
   WHERE r1.winner_id = r.winner_id
     OR r1.loser_id = r.winner_id
  GROUP BY IF(winner_id=r.winner_id, winner_id,loser_id)) win_streak

FROM ( SELECT winner_id
      FROM Results
      GROUP BY winner_id
      ) r

It returns not all ids now but only who had ever win. So to make it better, probably you have user table. If so it would simplify a query. If you have no user table you need to union all somehow users who had never win.

You are welcome if any questions.