Getting a win streak from a database

Question

I have followed this tutorial on trying to get a win streak from my database of bets. The data has a result (Win/Loss/Pending) and a date (Amongst other values) Here is the SQL command I'm using...

SELECT * 
FROM (SELECT Result, 
  MIN(date) as StartDate, 
  MAX(date) as EndDate, 
  COUNT(*) as Games
  FROM (SELECT *,
    (SELECT COUNT(*) 
    FROM bets G 
    WHERE G.result <> GR.result
    AND G.date <= GR.date) as RunGroup 
FROM bets GR WHERE user = 4 ORDER BY date DESC) A
GROUP BY result, RunGroup
ORDER BY Min(date)) A 
WHERE result = 'Win'
ORDER BY Games DESC

The only difference with mine is I'm trying to filter a single users bets and not everyones bets but...I can see in my DB that there are 3 Win's in a row but my output is 2. Can anyone spot where I have gone wrong?

I want to get one row with the largest streak, which all I think I would need to do at this point is add LIMIT 1

Thanks

Answer 1

If you want all sequential wins summarized in one row, then I would suggest handling this has a gaps-and-islands problem. A simple method is to count the cumulative number of non-wins. This then assigns a group to each group of wins that can be used for aggregation:

select user, grp, count(*) as num_in_sequence,
       min(date), max(date)
from (select b.*,
             sum(result <> 'Win') over (partition by user order by date) as grp
      from bets b
     ) b
where result = 'Win'
group by user, grp;

Answer 2

This is how I did it:

SELECT * FROM (SELECT result, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games FROM (SELECT date, result, (SELECT COUNT(*) FROM bets G WHERE G.result <> GR.result AND G.date <= GR.date AND user = ${uid}) as RunGroup FROM bets GR WHERE user = ${uid} ORDER BY date) A Where result = 'Win' GROUP BY result, RunGroup ORDER BY Min(date)) A ORDER BY Games DESC LIMIT 1