MySQL Winning Streak for one team

Question

I have an SQL query that returns the following table about a single team:

date         gameid     pointsfor     pointsagainst

2011-03-20   15         1             10
2011-03-27   17         7             3
2011-04-03   23         6             5
2011-04-10   30         5             4
2011-04-17   35         4             8
2011-05-01   38         8             1
2011-05-08   43         3             7
2011-05-15   48         6             2
2011-05-22   56         10            2
2011-05-29   59         4             5
2011-06-05   65         2             3
2011-06-19   71         5             6
2011-06-19   74         12            2
2011-06-19   77         5             2
2011-06-19   80         5             4

From this table, could anybody please help me calculate what the longest winning and losing streaks are?

I've had a look at a few other examples on here but have struggled to follow them as they are not quite the same as mine. Any help would be greatly appreciated. Thanks!

Answer 1

You'll have to implement some MySQL variables to help handle this more efficiently than multiple query/join/group by. This has a single pass through all the records, then once more getting the max per type of win/loss (or tie). I'm assuming the data you've provided is for a single time, and the date is the obvious order of the games... Points For is the team you are interested in, and points against is whoever the opponent is. That said, my "alias" name will be "YourResultSingleTeam".

The inner query will pre-determine the status of the game as "W"in or "L"oss. Then, see if that value is the same as the previous instance for the team. If so, add 1 to the existing win/loss counter. If not, set the counter back to 1. Then, preserve the status of the current game back into the "LastStatus" value for comparison to the next game.

After that is done, its a simple game result, max() grouped by the game result status

select
      StreakSet.GameResult,
      MAX( StreakSet.WinLossStreak ) as MaxStreak
   from
      ( select YR.Date,
               @CurStatus := if( YR.PointsFor > YR.PointsAgainst, 'W', 'L' ) as GameResult,
               @WinLossSeq := if( @CurStatus = @LastStatus, @WinLossSeq +1, 1 ) as WinLossStreak,
               @LastStatus := @CurStatus as carryOverForNextRecord
            from 
               YourResultSingleTeam YR,
               ( select @CurStatus := '',
                        @LastStatus := '',
                        @WinLossSeq := 0 ) sqlvars
            order by
               YR.Date ) StreakSet
   group by
      StreakSet.GameResult

As offered by Nikola, if you want to consider "tie" games, we can adjust by just changing the @CurStatus to the case/when condition to

@CurStatus := case when YR.PointsFor > YR.PointsAgainst then 'W'
                   when YR.PointsFor < YR.PointsAgainst then 'L'
                   else 'T' end as GameResult,

Answer 2

There is a solution but I don't think you are going to like it because it requires a self-join and your table is not a table but query.

Inner query will transform dates into ranges - that it, for each date in a table it will find first date having different outcame, or, in case of last game, the date of this game. This data will be aggregated by first date of different streak to flatten and count streaks; outer query then finds extremes by outcome.

select case Outcome 
            when -1 then 'Losses'
            when 1 then 'Wins'
            else 'Undecided'
        end Title
      , max(Streak) Streak
from
(
  select min(date) date, date_to, Outcome, count(*) Streak
  from
  (
    select t1.date, 
           sign (t1.pointsfor - t1.pointsagainst) Outcome, 
           ifnull (min(t2.date), t1.date) date_to
     from table1 t1
     left join table1 t2
       on t1.date < t2.date
      and sign (t1.pointsfor - t1.pointsagainst) 
       <> sign (t2.pointsfor - t2.pointsagainst)
    group by t1.date, sign (t1.pointsfor - t1.pointsagainst)
  ) a
  group by date_to, Outcome
) a
group by Outcome

To circumvent the need to replace table1 with - probably - cumbersome query you might use temporary table, or have the data already in appropriate format in auxiliary table. There is live test at Sql fiddle, along with another, subquery-driven version that might perform better - you should try them both.

Answer 3

Latest version of MySQL has CTE and is windowing-capable.

Here's a solution.

First step, group winning and losing by assigning them their own streak_group number:

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
select *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
from t

Output:

date                |gameid |pointsfor |pointsagainst |is_winner |is_new_group |streak_group |
--------------------|-------|----------|--------------|----------|-------------|-------------|
2011-03-20 15:00:00 |15     |1         |10            |0         |1            |1            |
2011-03-27 15:00:00 |17     |7         |3             |1         |1            |2            |
2011-04-03 15:00:00 |23     |6         |5             |1         |0            |2            |
2011-04-10 15:00:00 |30     |5         |4             |1         |0            |2            |
2011-04-17 15:00:00 |35     |4         |8             |0         |1            |3            |
2011-05-01 15:00:00 |38     |8         |1             |1         |1            |4            |
2011-05-08 15:00:00 |43     |3         |7             |0         |1            |5            |
2011-05-15 15:00:00 |48     |6         |2             |1         |1            |6            |
2011-05-22 15:00:00 |56     |10        |2             |1         |0            |6            |
2011-05-29 15:00:00 |59     |4         |5             |0         |1            |7            |
2011-06-05 15:00:00 |65     |2         |3             |0         |1            |8            |
2011-06-19 15:00:00 |74     |12        |2             |1         |1            |9            |
2011-06-19 15:00:00 |77     |5         |2             |1         |0            |9            |
2011-06-19 15:00:00 |80     |5         |4             |1         |0            |9            |
2011-06-19 15:00:00 |71     |5         |6             |0         |1            |10           |

Final query. Count the streak of winning:

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
, streak_grouping as
(
    select
        *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
    from t
)
select 
    min(date) as start_date,
    max(date) as end_date,
    count(*) as streak,
    group_concat(gameid order by gameid) as gameid_list
from streak_grouping
group by streak_group
order by streak desc, start_date

Output:

start_date          |end_date            |streak |gameid_list |
--------------------|--------------------|-------|------------|
2011-03-27 15:00:00 |2011-04-10 15:00:00 |3      |17,23,30    |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |3      |74,77,80    |
2011-05-15 15:00:00 |2011-05-22 15:00:00 |2      |48,56       |
2011-03-20 15:00:00 |2011-03-20 15:00:00 |1      |15          |
2011-04-17 15:00:00 |2011-04-17 15:00:00 |1      |35          |
2011-05-01 15:00:00 |2011-05-01 15:00:00 |1      |38          |
2011-05-08 15:00:00 |2011-05-08 15:00:00 |1      |43          |
2011-05-29 15:00:00 |2011-05-29 15:00:00 |1      |59          |
2011-06-05 15:00:00 |2011-06-05 15:00:00 |1      |65          |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |1      |71          |