CODEWITHSUNDEEP
sqlsql-serverrdbms
jebaseelan ravi
jebaseelan ravi

Reputation: 225

Maximum consecutive row in sql

I am very new to SQL. I have two tables related to two player Tennis Game

Table 1 Name: matches # match information

tourney_id  tourney_name    surface tourney_date  mid winnner_id  loser_id  score   
-------------------------------------------------------------------------------------
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  270   201504  201595  "5-7 6-1 6-3"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  271   201514  201426  "6-3 6-4"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  272   201697  211901  "6-2 7-6(4)"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  273   201620  211539  "6-1 6-0"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  274   214981  203564  "6-2 7-6(2)"

Table 2: players #information about the players

playerid  First Name    Last Name   Country
--------------------------------------------
200001     Martina      Hingis        SUI
200002     Mirjana      Lucic         CRO
200003     Justine      Henin         BEL

You can find complete table contents table1-matches

I need to find out which player(s) won the highest number of matches in a row?

I tired this SQL query using window function but could not succeed

select 
    *,
    count(tourney_id) over (partition by winner_id),
    count(tourney_id) over (partition by tourney_date)
from 
    matches
order by 
    tourney_date

Thanks in advance!

Upvotes: 2

Views: 135

Answers (1)

Charlieface
Charlieface

Reputation: 71178

this is a type of gaps-and-islands problem, with the added complication that we first need to unpivot the wins and losses in order to get the start of each island.

We can use LAG to check the previous match for each player. Then a windowed count gives us a number for each group of wins.

WITH PrevValues AS (
    SELECT
      v.status,
      v.id,
      m.tourney_date,
      StartOfGroup = CASE WHEN LAG(v.status, 1, '.')
              OVER (PARTITION BY v.id ORDER BY m.tourney_date) <> v.status THEN 1 END
    FROM matches m
    CROSS APPLY (VALUES
      ('W', winnner_id),
      ('L', loser_id)
    ) v(status, id)
),
Groups AS (
    SELECT *,
      GroupId = COUNT(*) OVER (PARTITION BY v.id
                ORDER BY m.tourney_date ROWS UNBOUNDED PRECEDING)
    FROM PrevValues v
    WHERE v.status = 'W'
),
PerGroup AS (
    SELECT
      v.id,
      TotalWins = COUNT(*)
    FROM Groups g
    GROUP BY
      g.id,
      g.GroupId
)
SELECT
  p.id,
  MaxConsecutiveWins = MAX(TotalWins)
FROM PerGroup p
GROUP BY
  p.id;

Upvotes: 1

Related Questions