CODEWITHSUNDEEP
sqlgaps-and-islandscockroachdb
Morten Stensgaard
Morten Stensgaard

Reputation: 13

Consecutive streaks/non-streaks based on Win, Draw & Loss grouped by team

I am trying to calculate streaks across a result database based on win loss and draw criteria.

Goal: Get longest streak of wins/non-wins grouped by team

I have tried different SQL query suggestions from other threads, but I am either missing out the grouping or the team column and often only takes 2-way option(Win and loss) - I need 3-way option(Win, Los, Draw incl. non-win,non-loss and non-draw)

Looked at this - https://www.sqlteam.com/articles/detecting-runs-or-streaks-in-your-data

But I have no clue how to get the team incl. grouping into the mix

Scheme:

CREATE TABLE teamresults (matchid varchar(255), date DATE, time TIME, team varchar(255), teamresult varchar(255))

Data sample:

INSERT INTO teamresults (matchid,"date","time",team,teamresult) VALUES 
('030420181800acfc','2018-04-03','18:00:00','AC Horsens','L')
,('080420181600brac','2018-04-08','16:00:00','AC Horsens','L')
,('150420181400aaac','2018-04-15','14:00:00','AC Horsens','L')
,('180420181800acfc','2018-04-18','18:00:00','AC Horsens','D')
,('210420181600fcac','2018-04-21','16:00:00','AC Horsens','L')
,('270420181900acfc','2018-04-27','19:00:00','AC Horsens','L')
,('040520181900acaa','2018-05-04','19:00:00','AC Horsens','W')
,('110520181900fcac','2018-05-11','19:00:00','AC Horsens','L')
,('180520182000acbr','2018-05-18','20:00:00','AC Horsens','D')
,('210520181800fcac','2018-05-21','18:00:00','AC Horsens','L')
,('120520191200veac','2019-05-12','12:00:00','AC Horsens','W')
,('190520191400acve','2019-05-19','14:00:00','AC Horsens','D')
,('140720191400acfc','2019-07-14','14:00:00','AC Horsens','L')
,('210720191200siac','2019-07-21','12:00:00','AC Horsens','W')
,('270720191730acfc','2019-07-27','17:30:00','AC Horsens','L')
,('040820191600brac','2019-08-04','16:00:00','AC Horsens','W')
,('010420181400hoag','2018-04-01','14:00:00','AGF','W')
,('080420181800agsi','2018-04-08','18:00:00','AGF','W')
,('130420181900agfc','2018-04-13','19:00:00','AGF','W')
,('170420181900fcag','2018-04-17','19:00:00','AGF','L')
,('230420181900agho','2018-04-23','19:00:00','AGF','L')
,('300420181900siag','2018-04-30','19:00:00','AGF','W')
,('060520181200agob','2018-05-06','12:00:00','AGF','W')
,('130520181800obag','2018-05-13','18:00:00','AGF','W')
,('190520181600ags�','2018-05-19','16:00:00','AGF','D')
;

The below query does work, but does only a take single input statement - So I can only get streak of win, loss or draws - Not non-win, non-loss and non-draws.

SELECT
   team,
   MAX(cnt)
FROM
 (

SELECT
      team,
      COUNT(*) AS cnt
   FROM 
    (

SELECT
        team, 
        date,
        teamresult,
        SUM(CASE WHEN teamresult <> 'W'  THEN 1 else 0 END) 
        OVER (PARTITION BY team 
              ORDER BY date 
              ROWS UNBOUNDED PRECEDING) AS dummy
      FROM teamresults

      ) dt
         WHERE teamresult = 'W' 
   GROUP BY team, dummy
 ) dt
GROUP BY team;

I also want to be able to find longest non-streaks grouped by team

SQL fiddle is available here: http://sqlfiddle.com/#!18/3a2ac/1

Thanks in advance

Update: Gordon queries are working, but these queries does not work in postgres/cockroach - So now trying to convert them to supported queries via window functions rank()

select team, teamresult, cnt, rank() over (order by cnt desc) from
(SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
             ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)) as ranked

This does gives me an output like this(Data sample from my DB):

FC København    W   9   1
AaB             W   8   2
FC København    W   8   2
FC København    W   8   2
FC København    W   8   2
Brøndby IF      W   7   6
FC Midtjylland  W   7   6
FC København    W   7   6
FC København    W   7   6
FC København    W   7   6
Esbjerg fB      W   6   11
FC Midtjylland  W   6   11
AaB             W   6   11
Brøndby IF      W   6   11
Brøndby IF      W   6   11

Expected output:

Team           Longest consecutive streak
FC København       9
AaB                8
Brøndby IF         7
FC Midtjylland     7
Esbjerg fB         6

Upvotes: 0

Views: 746

Answers (2)

Morten Stensgaard
Morten Stensgaard

Reputation: 13

Thanks to Gordon, I solved my case with the following queries:

Longest consecutive streak of Wins(replace W with D for draw, and L for loss) grouped by team

select team, max(cnt) longeststreak from (

SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
             RANK() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             RANK() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY RANK() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)

)
group by team
order by longeststreak DESC

Longest consecutive streak of non-wins(replace W with D for non-draw, and L for non-loss) grouped by team

select team, max(cnt) longestnonstreak from (

SELECT team, 
       (CASE WHEN teamresult = 'W' THEN 'W' END) as is_win,
       COUNT(*) as cnt
FROM (SELECT tr.*,
             RANK() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             RANK() OVER (PARTITION BY team, (CASE WHEN teamresult = 'W' THEN 'W' END) ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
GROUP BY team, (CASE WHEN teamresult = 'W' THEN 'W' END), (seqnum - seqnum_r)
ORDER BY RANK() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)

)
group by team
order by longestnonstreak desc

Thanks to Gordon for assisting in the solution.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can get all the streaks using:

SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
             ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
GROUP BY team, teamresult, (seqnum - seqnum_r);

You can modify this to get the longest winning streak per team:

SELECT TOP(1) WITH TIES team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
             ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC);

If you just want the longest streak of any type, remove the WHERE. If you want the longest for each type per team, then add teamresult to the PARTITION BY.

Here is a db<>fiddle.

EDIT:

If you want non-wins, you need to partition by an expression:

SELECT TOP(1) WITH TIES team,
       (CASE WHEN teamresult = 'W' THEN 'W' END) as is_win,
       COUNT(*) as cnt
FROM (SELECT tr.*,
             ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
             ROW_NUMBER() OVER (PARTITION BY team, (CASE WHEN teamresult = 'W' THEN 'W' END) ORDER BY "date", "time") as seqnum_r
      FROM teamresults tr
     ) tr
-- WHERE teamresult = 'W'
GROUP BY team, (CASE WHEN teamresult = 'W' THEN 'W' END), (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)

Upvotes: 1

Related Questions