Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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