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

Question

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

define `calculate streaks` Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Aug 10 '19 at 13:34
Thx for feedback - Have edited my post to be more precise on expected outcome, incl. current scheme, data and query. — Morten Stensgaard, Aug 10 '19 at 13:43

Gordon Linoff · Accepted Answer · 2019-08-10T19:29:32.100

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)

Thanks - But unfortunately is breaks on my DB(cockroach)SQL Error [42601]: ERROR: syntax error at or near "with" Detail: source SQL: SELECT TOP(1) WITH TIES team, teamresult, COUNT(*) as cnt — Morten Stensgaard, Aug 10 '19 at 14:27
How will a non-win streak query look like? Replaceing = with <> for non-wins, return 3 for Horsens, whereas the max streak in the dataset is actually 6. — Morten Stensgaard, Aug 10 '19 at 14:38
Thx for the non-wins :-) - How do I convert the query to support cockroach/postgres? - They do not support 'with ties' - instead they support Rank() as windows function. Looked at this https://stackoverflow.com/questions/9629953/postgresql-equivalent-for-top-n-with-ties-limit-with-ties, but can't find the logic for how to change the query. — Morten Stensgaard, Aug 10 '19 at 22:41
@MortenStensgaard . . . You can use `rank()` instead of `row_number()` if there are no ties. — Gordon Linoff, Aug 11 '19 at 02:07

score 1 · Answer 2 · answered Aug 11 '19 at 14:26

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.

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

2 Answers2