Tags

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


T-SQL Query | [ The Consecutive Wins Puzzle – 2 ]

Original Puzzle URL – http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23880

Puzzle Statement

  1. We have a table called GameResults.
  2. Here we have to find the winning streak of a player(s) that spans multiple events/dates. For example…the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.
  3. Please check out the sample input and expected output for details.

Sample Input

TournamentId PlayerId GameDate Result
1 1000 01-01-2000 W
1 1000 01-01-2000 L
1 1000 01-01-2000 W
1 1000 01-01-2000 W
1 1000 01-01-2000 L
1 2000 01-01-2000 L
1 2000 01-01-2000 W
1 2000 01-01-2000 L
1 2000 01-01-2000 L
1 2000 01-01-2000 W
2 1000 01-02-2000 W
2 1000 01-02-2000 W
2 1000 01-02-2000 W
2 1000 01-02-2000 W
2 1000 01-02-2000 L
2 2000 01-02-2000 W
2 2000 01-02-2000 W
2 2000 01-02-2000 W
2 2000 01-02-2000 W
2 2000 01-02-2000 L
3 3000 15-02-2000 L
3 3000 15-02-2000 L
3 3000 15-02-2000 W
3 3000 15-02-2000 W
3 3000 15-02-2000 W
3 2000 15-02-2000 L
3 2000 15-02-2000 L
3 2000 15-02-2000 W
3 2000 15-02-2000 W
3 2000 15-02-2000 W

Expected Output

PlayerId ConsecutiveWins
1000 4
2000 5
3000 3

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 [dbo].[GameResults](
	[TournamentId] [int] NULL,
	[PlayerId] [int] NULL,
	[GameDate] [date] NULL,
	[Result] [varchar](5) NULL
)

INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES 
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W')

--

Update May 6 | Solution 1


--


;WITH CTE1 AS
(
    SELECT * , ROW_NUMBER() OVER  (PARTITION BY PlayerId ORDER BY GameDate) rnk 
    FROM GameResults	
)
,CTE2 AS
(
    SELECT *, 
        CASE WHEN Result = lag(Result) 
        over(PARTITION BY PlayerId order by rnk) THEN 0 ELSE 1 END cols 
    FROM CTE1 c2         
)
,CTE3 AS
(
	SELECT * , SUM(cols) OVER (PARTITION BY PlayerId ORDER BY rnk) grouper FROM CTE2
)
,CTE4 AS
(
	SELECT PlayerId, grouper , COUNT(grouper) ConsecutiveWins FROM CTE3
	GROUP BY PlayerId,grouper
)
SELECT PlayerId , MAX(ConsecutiveWins) ConsecutiveWins FROM CTE4 GROUP BY PlayerId





--

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

Keep Learning

http://MSBISkills.com