How to get three wins in a row

Question

Here is the table:

([TeamA],[TeamB],[Win],[date])

('KKR','HYD','KKR',1), 
('KKR','MUM','MUM',2), 
('RCB','HYD','HYD',3), 
('DEL','PUB','PUB',4), 
('RR','PUB','RR',4), 
('RR','DEL','RR',5),
('RCB','CSK','CSK',6),
('RR','CSK','RR',7),
('CSK','MUM','MUM',7),
('MUM','DEL','MUM',8),
('HYD','PUNE','PUNE',9),
('PUB','DEL','DEL',9),
('KKR','DEL','KKR',10),
('KKR','RCB','KKR',10)

The required answer should be the teams who are winning 3 in a row and the count. Here for eg RR and MUM are winning once 3 in a row. KKR has 3 wins however if we see the date column it is not 3 in a row hence KKR should not be in the answer and the output should be

RR 1
MUM 1

Answer 1

My approach (probably it can be done in cleaner way):

WITH cte AS
(
  SELECT TeamA AS team FROM #tab
  UNION 
  SELECT TeamB FROM #tab
), cte2 AS 
(
  SELECT c.team
      ,[opponent] = CASE WHEN c.team = t.teamA THEN t.teamB ELSE t.teamA END
      ,t.[win]
      ,t.[day]
      ,[is_winner] = CASE WHEN c.team = t.[win] THEN 1 ELSE 0 END
  FROM cte c
  JOIN #tab t
    ON c.team = t.teamA
    OR c.team = t.teamB
), cte3 AS
(
  SELECT team, [day], [is_winner],
    r =  ROW_NUMBER() OVER (PARTITION BY team ORDER BY [day])
  FROM cte2
), cte4 AS
(
  SELECT team, Length = MAX(r) - MIN(r) + 1 
  FROM (SELECT team, r
          ,rn=r-ROW_NUMBER() OVER (PARTITION BY team ORDER BY r)
        FROM cte3
        WHERE is_winner = 1) a
  GROUP BY team, rn
)
SELECT team, SUM(Length/3) AS [Number_of_hat_tricks] 
FROM cte4
WHERE Length >= 3
GROUP BY team;

LiveDemo

Output:

╔══════╦══════════════════════╗
║ team ║ Number_of_hat_tricks ║
╠══════╬══════════════════════╣
║ MUM  ║                    1 ║
║ RR   ║                    1 ║
╚══════╩══════════════════════╝

How it works:

• cte - get all teams
• cte2 - for each team find opponent and check if team wins
• cte3 - add consequitive numbers
• cte4 - calculate length of each island
• final - get the island >= 3 and sum them up (integer division is for counting 6 wins in rows as 2 and 9 in rows as 3,...)

One final thought:

Value in in last column has to be unique with the same team:

('RR','CSK','RR',7)
('CSK','MUM','MUM',7)

CSK vs RR - 7
CSK vs MUM - 7

With current data is impossible to sort it in stable manner. So it should be date with time part:

CSK vs RR  2015-12-07 10:00
CSK vs MUM 2015-12-07 21:00       -- now we know that it is the second match

Answer 2

Gaps-and-islands. Calculate the length of each island. Final count is length of island divided by 3 (integer division, discarding fractional part).

I added few more rows (teams A and B) to illustrate 4 wins of A, then 7 wins of B, then 4 wins of A again, which results in final count of 2 and 2 for both A and B.

Sample data

DECLARE @T TABLE (TeamA varchar(50), TeamB varchar(50), Win varchar(50), dt int);

INSERT INTO @T (TeamA, TeamB, Win, dt) VALUES
('KKR','HYD','KKR',1), 
('KKR','MUM','MUM',2), 
('RCB','HYD','HYD',3), 
('DEL','PUB','PUB',4), 
('RR','PUB','RR',4), 
('RR','DEL','RR',5),
('RCB','CSK','CSK',6),
('RR','CSK','RR',7),
('CSK','MUM','MUM',7),
('MUM','DEL','MUM',8),
('HYD','PUNE','PUNE',9),
('PUB','DEL','DEL',9),
('KKR','DEL','KKR',10),
('KKR','RCB','KKR',10),

('A','B','A',11),
('A','B','A',12),
('A','B','A',13),
('A','B','A',14),

('A','B','B',15),
('A','B','B',16),
('A','B','B',17),
('A','B','B',18),
('A','B','B',19),
('A','B','B',20),
('A','B','B',21),

('A','B','A',22),
('A','B','A',23),
('A','B','A',24),
('A','B','A',25);

Query

Normally you would have a list of teams in a separate table, here I build it in a CTE_Teams. CTE_Counts has number of 3-wins-in-a-row for each consecutive winning streak. As there could be multiple winning streaks for a team (see team A), this is summed up further. Teams can be listed in any order in columns TeamA and TeamB, so in the WHERE inside the CROSS APPLY there is an OR to capture both variants.

So, for each team select only those rows that are relevant for this team. This is done by CROSS APPLY.

Then, classic gaps-and-islands by numbering rows twice with different partitioning. Difference in row numbers give the groups (islands and gaps).

Filter WHERE CTE_Teams.Team = CA.Win leaves only islands of winning teams.

Grouping by CTE_Teams.Team gives the size of the island, i.e. number of wins in a row.

Query works in SQL Server 2008.

WITH
CTE_Teams
AS
(
    SELECT T.TeamA AS Team
    FROM @T AS T

    UNION -- sic! not ALL

    SELECT T.TeamB AS Team
    FROM @T AS T
)
,CTE_Counts
AS
(
    SELECT
        CTE_Teams.Team
        --,CA.Win
        --,rn1 - rn2 AS GroupNumber
        --,COUNT(*) AS GroupSize
        ,COUNT(*) / 3 AS FinalCount
    FROM
        CTE_Teams
        CROSS APPLY
        (
            SELECT
                T.Win
                ,T.dt
                ,ROW_NUMBER() OVER (PARTITION BY CTE_Teams.Team 
                    ORDER BY T.dt, T.TeamA, T.TeamB) AS rn1
                ,ROW_NUMBER() OVER (PARTITION BY CTE_Teams.Team, T.Win 
                    ORDER BY T.dt, T.TeamA, T.TeamB) AS rn2
            FROM @T AS T
            WHERE
                T.TeamA = CTE_Teams.Team
                OR T.TeamB = CTE_Teams.Team
        ) AS CA
    WHERE
        CTE_Teams.Team = CA.Win
    GROUP BY
        CTE_Teams.Team
        ,CA.Win
        ,rn1 - rn2
    HAVING COUNT(*) / 3 > 0
)
SELECT
    CTE_Counts.Team
    ,SUM(CTE_Counts.FinalCount) AS FinalCount
FROM CTE_Counts
GROUP BY CTE_Counts.Team
ORDER BY CTE_Counts.Team;

Result

+------+------------+
| Team | FinalCount |
+------+------------+
| A    |          2 |
| B    |          2 |
| MUM  |          1 |
| RR   |          1 |
+------+------------+

SQL Fiddle