0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #tab([TeamA] VARCHAR(100),[TeamB] VARCHAR(100),
    [Win] VARCHAR(100),[day] INT)


INSERT INTO #tab 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);

/*
WITH cte AS
(
SELECT Team1 = CASE WHEN TeamA < TeamB THEN TeamA ELSE TeamB END
      ,Team2 = CASE WHEN TeamB < TeamA THEN TeamA ELSE TeamB END
      ,Win
      ,[Day]
FROM  #tab
)
*/

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

fork query

edited dec 16 15

created dec 16 15 anonymous