Calculating win streak from list of fixtures

I have a table fixtures with a list of matches played between two players in each, identified by their ID. I’m trying to use the table to calculate rolling winning/losing streaks for each player, but I’ve been having issues with my query below (see more on SQL Fiddle):

SELECT date, edition_id, player_id, res,
  ROW_NUMBER() OVER (
    PARTITION BY player_id, prev_res, res
    ORDER BY edition_id
  ) AS seq
FROM (
  SELECT date, edition_id, player_id, prev, res, prev_res,
    ROW_NUMBER() OVER(PARTITION BY player_id) AS num,
    -1 + ROW_NUMBER() OVER(PARTITION BY player_id) AS prev_num
  FROM (
    SELECT date, edition_id,
      player_id, LAG(player_id,1) OVER() AS prev,
      res, LAG(res, 1) OVER() AS prev_res
    FROM (
      SELECT date, edition_id, player1_id AS player_id,
        CASE WHEN score1 > score2 THEN 'W' ELSE 'L' END AS res
      FROM fixtures
      WHERE edition_id BETWEEN 0 AND 5
      UNION
      SELECT date, edition_id, player2_id AS player_id,
        CASE WHEN score2 > score1 THEN 'W' ELSE 'L' END AS res
      FROM fixtures
      WHERE edition_id BETWEEN 0 AND 5
      ORDER BY 3, 2
    ) AS results
  ) X
) Y

Below is the desired result, with records partitioned by player_id and ordered by edition_id. seq restarts the count every time res changes from a win to a loss or vice versa, and continues otherwise:

-----------+------------+-----------+-----+-----+
date       | edition_id | player_id | res | seq |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      1010 |   L |   1 |
2020-06-26 |          2 |      1010 |   L |   2 |
2020-07-10 |          3 |      1010 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      1212 |   W |   1 |
2020-06-26 |          2 |      1212 |   W |   2 |
2020-07-24 |          4 |      1212 |   W |   3 |
2020-08-14 |          5 |      1212 |   W |   4 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      2020 |   L |   1 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      2424 |   W |   1 |
2020-06-26 |          2 |      2424 |   W |   2 |
2020-07-10 |          3 |      2424 |   L |   1 |
2020-08-14 |          5 |      2424 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-26 |          2 |      3131 |   W |   1 |
2020-07-10 |          3 |      3131 |   W |   2 |
2020-07-24 |          4 |      3131 |   L |   1 |
2020-08-14 |          5 |      3131 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      3535 |   W |   1 |
2020-06-26 |          2 |      3535 |   W |   2 |
2020-07-24 |          4 |      3535 |   L |   1 |
2020-08-14 |          5 |      3535 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-26 |          2 |      4242 |   L |   1 |
2020-07-10 |          3 |      4242 |   L |   2 |
2020-08-14 |          5 |      4242 |   L |   3 |
-----------+------------+-----------+-----+-----+
2020-07-10 |          3 |      4646 |   L |   1 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      5353 |   L |   1 |
2020-07-10 |          3 |      5353 |   L |   2 |
2020-07-24 |          4 |      5353 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-07-24 |          4 |      5757 |   L |   1 |
2020-08-14 |          5 |      5757 |   L |   2 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      6464 |   L |   1 |
-----------+------------+-----------+-----+-----+
2020-07-24 |          4 |      6868 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-26 |          2 |      7575 |   L |   1 |
2020-07-10 |          3 |      7575 |   W |   1 |
2020-07-24 |          4 |      7575 |   L |   1 |
2020-08-14 |          5 |      7575 |   W |   1 |
-----------+------------+-----------+-----+-----+
2020-06-12 |          1 |      7979 |   W |   1 |
2020-06-26 |          2 |      7979 |   L |   1 |
2020-07-10 |          3 |      7979 |   L |   2 |
2020-07-24 |          4 |      7979 |   L |   3 |
2020-08-14 |          5 |      7979 |   L |   4 |

How should I fix my query to achieve this?

Answer

This requires a stock gaps and islands solution. Start by numbering each partition and subtract numbering partitioned by each result to identify groups of repeating sequences.

You can then use this grouping to further partition by to generate the required sequence:

with games as (
    select date, edition_id, player1_id as player_id,
        case when score1 > score2 then 'W' else 'L' end as res
    from fixtures
    where edition_id between 0 and 5
    union all
    select date, edition_id, player2_id as player_id,
        case when score2 > score1 then 'W' else 'L' end as res
    from fixtures
    where edition_id between 0 and 5
), grp as (
    select * , 
        Row_Number() over(partition by player_id order by edition_id)
         - Row_Number() over(partition by res, player_id order by edition_id) as gp
    from games
)
select date, edition_id, player_id, res,
    Row_Number() over(partition by player_id, res, gp order by edition_id) seq
from grp
order by 3, 2

See updated Fiddle

Advertisement

Answer