Winning Streak per player per event MySQL

Question

I have tried the different solutions with the "winning streak" search criteria, but I can't seen to fit them in my situation.

I have the following tables:

BetHistory:

+-------+-------+-----------+
|   id  | betid | betstatus |
+-------+-------+-----------+
| 63281 | 63280 | lose      |
| 63280 | 63279 | won       |
| 63279 | 63278 | lose      |
| 63278 | 63277 | lose      |
| 63276 | 63275 | won       |
| 63275 | 63274 | won       |
| 63271 | 63270 | won       |
| 63270 | 63269 | won       |
| 63269 | 63268 | won       |
| 63268 | 63267 | won       |
| 63267 | 63266 | lose      |
| 63266 | 63265 | won       |
| 63264 | 63263 | won       |
| 63262 | 63261 | won       |
| 63261 | 63260 | lose      |
| 63259 | 63258 | lose      |
| 63258 | 63257 | won       |
| 63257 | 63256 | lose      |
| 63256 | 63255 | lose      |
| 63254 | 63253 | won       |
+-------+-------+-----------+

Bets:

+-------+--------+----------+
| betid | gameid | username |
+-------+--------+----------+
| 63280 |   7506 | player1  |
| 63279 |   7506 | player2  |
| 63278 |   7506 | player5  |
| 63277 |   7506 | player2  |
| 63275 |   7506 | player12 |
| 63274 |   7506 | player12 |
| 63270 |   7506 | player5  |
| 63269 |   7505 | player2  |
| 63268 |   7505 | player12 |
| 63267 |   7505 | player2  |
| 63266 |   7505 | player5  |
| 63265 |   7505 | player1  |
| 63263 |   7505 | player2  |
| 63261 |   7504 | player2  |
| 63260 |   7504 | player5  |
| 63258 |   7504 | player5  |
| 63257 |   7504 | player2  |
| 63256 |   7504 | player2  |
| 63255 |   7503 | player2  |
| 63253 |   7503 | player1  |
+-------+--------+----------+

EventGames: - another table that stores relation between games and events, which could easily be integrated, as needed.

I need help in framing the MySQL statement to determine the players that have at least five (5) straight wins.

Much thanks in advance.

Edit: the table below may be easier:

+-------+-----------+--------+----------+------+
| id    | betstatus | gameid | username | team |
+-------+-----------+--------+----------+------+
| 63281 | lose      |   7506 | player12 | C    |
| 63280 | won       |   7506 | player7  | A    |
| 63279 | lose      |   7506 | player3  | B    |
| 63278 | lose      |   7506 | player10 | B    |
| 63276 | won       |   7506 | player1  | A    |
| 63275 | won       |   7506 | player1  | A    |
| 63271 | won       |   7506 | player3  | A    |
| 63270 | won       |   7505 | player7  | B    |
| 63269 | won       |   7505 | player1  | B    |
| 63268 | won       |   7505 | player10 | B    |
| 63267 | lose      |   7505 | player3  | A    |
| 63266 | won       |   7505 | player12 | B    |
| 63264 | won       |   7505 | player10 | B    |
| 63262 | won       |   7504 | player10 | B    |
| 63261 | lose      |   7504 | player3  | A    |
| 63259 | lose      |   7504 | player3  | A    |
| 63258 | won       |   7504 | player10 | B    |
| 63257 | lose      |   7504 | player7  | A    |
| 63256 | lose      |   7503 | player7  | A    |
| 63254 | won       |   7503 | player12 | B    |
+-------+-----------+--------+----------+------+

Answer 1

I tried using the answers posted, and I thank you all for the bright ideas.

Complex as it is to check for streaks, I opted to just add an listener that whenever a game announced the winner, winning sides's streak are incremented and the losers will start with 0.

I then can just simply query the table with the streak condition.

Much thanks,

Answer 2

This sql is applied to united table which is named BetHistory from your Edit. The idea used is taken from @Matt's comment.

select Distinct(t.username)
from
    (select bh1.username, bh1.id, count(bh2.id) as streakGroup 
    from BetHistory bh1
    left join BetHistory bh2
    on bh1.id < bh2.id and bh1.username = bh2.username and bh1.betstatus <> bh2.betstatus
    where bh1.betstatus = 'won'
    group by bh1.id, bh1.username) as t
group by t.streakGroup, t.username
having Count(t.id) >= 5

Demo here. team and gameid columns are omitted as they are not used.

Answer 3

You can use variables to get the desired result:

SELECT username, MAX(streak) AS streak
FROM (
  SELECT username, betstatus, id,
         @streak := IF(betstatus = 'lose', IF(@uid := username, 0, 0),
                      IF(@uid = username, @streak + 1,
                         IF(@uid := username, 1, 1))) AS streak

  FROM (
    SELECT b.username, bh.betstatus, bh.id
    FROM Bets AS b
    INNER JOIN BetHistory AS bh ON b.betid = bh.betid) AS t 
  CROSS JOIN (SELECT @streak := 0, @uid := '') AS vars
  ORDER BY username, id) AS s
WHERE streak >= 5  
GROUP BY username 
ORDER BY username 

Note: I have used the original table structure.

Note 2: The query uses nested IF expressions to properly initialize and access variables.

Demo here