T-SQL Query | [ Consecutive Wins for India Puzzle ]  – In this puzzle we have to find the maximum consecutive wins for India in India Vs Australia series. Please check out the sample input and expected output for details.

Sample Input

 TeamA TeamB MatchDate WinsBy Ind Aus 10-01-2014 Ind Ind Aus 15-01-2014 Ind Ind Aus 19-01-2014 Ind Ind Aus 23-01-2014 Aus Ind Aus 27-01-2014 Ind Ind Aus 31-01-2014 Ind

Expected Output

 TeamA TeamB MatchDate WinsBy Ind Aus 10-01-2014 Ind Ind Aus 15-01-2014 Ind Ind Aus 19-01-2014 Ind

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create table CREATE TABLE IndAusSeries ( TeamA VARCHAR(3) ,TeamB VARCHAR(3) ,MatchDate DATETIME ,WinsBy VARCHAR(3) ) GO --Insert Data INSERT INTO IndAusSeries(TeamA,TeamB,MatchDate,WinsBy) VALUES ('Ind','Aus','01-10-2014','Ind'), ('Ind','Aus','01-15-2014','Ind'), ('Ind','Aus','01-19-2014','Ind'), ('Ind','Aus','01-23-2014','Aus'), ('Ind','Aus','01-27-2014','Ind'), ('Ind','Aus','01-31-2014','Ind') --Verify Data SELECT TeamA,TeamB,MatchDate,WinsBy FROM IndAusSeries ```

Update Apr 28 | Solution 1

 ``` -- -----------Method 1 | Pawan Kumar Khowal ---------------- ;WITH CTE1 AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk FROM IndAusSeries ) ,CTE2 AS ( SELECT *, CASE WHEN WinsBy = lag(WinsBy) over(order by rnk) THEN 0 ELSE 1 END cols FROM CTE1 c2 ) ,CTE3 AS ( SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper FROM CTE2 ) ,CTE4 AS ( SELECT grouper,COUNT(grouper) Maxy FROM CTE3 GROUP BY grouper ) ,CTE5 AS ( SELECT MAX(Maxy) Maxy FROM CTE4 ) SELECT c.TeamA , c.TeamB , c.MatchDate , c.WinsBy FROM CTE4 a INNER JOIN CTE5 b ON a.Maxy = b.Maxy INNER JOIN CTE3 c ON c.grouper = a.grouper -- ```

Update Apr 28 | Solution 2

 ``` -- -----------Method 2 | Deepak Sharma ---------------- WITH CTE AS ( SELECT * , RANK() OVER (PARTITION BY a.WinsBy,a.lag ORDER BY a.MatchDate) rnk , ROW_NUMBER() OVER (ORDER BY MatchDate) rnum FROM ( SELECT *, REPLACE(lag(winsby,1,0) OVER (ORDER BY MatchDate),'0','Ind') lag FROM IndAusSeries ) a ) SELECT TeamA,TeamB,MatchDate,WinsBy from CTE where rnk = rnum -- ```

Update Apr 28 | Solution 3

 ``` -- -----------Method 3 | Isha Mattoo ---------------- ;WITH CTE AS ( SELECT TeamA,TeamB,MatchDate,WinsBy ,ISNULL(Lead(WinsBy) OVER (ORDER BY (SELECT NULL)),0) AS NextWin FROM IndAusSeries ) ,CTE2 AS ( SELECT *,IIF(NextWin<> WinsBy,1,0) AS a ,ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) AS b from cte ) ,CTE3 AS ( SELECT *, b - ISNULL(lag(b) OVER (ORDER BY (SELECT NULL)),0) AS C FROM CTE2 WHERE a = 1 ) SELECT t2.* FROM cte3 t1 RIGHT OUTER JOIN IndAusSeries t2 ON t1.MatchDate = t2.MatchDate WHERE t2.MatchDate <= (SELECT MatchDate FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3)) AND t2.WinsBy = (SELECT WinsBy FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3)) -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com