Ok, I hope I got your attention with this title 🙂

I want to share my solution of an interesting problem I saw today at tek-tips forum SQL 2005: Find longest sequence of events within a table

Given the following table:

Team Table
Team Date WiLose
A 2010-01-01 00:00:00.000 Win
A 2010-01-07 00:00:00.000 Lose
A 2010-01-13 00:00:00.000 Lose
A 2010-01-19 00:00:00.000 Lose
A 2010-01-25 00:00:00.000 Lose
A 2010-01-31 00:00:00.000 Lose
A 2010-02-06 00:00:00.000 Win
A 2010-02-12 00:00:00.000 Lose
A 2010-02-18 00:00:00.000 Win
B 2010-01-01 00:00:00.000 Lose
B 2010-01-07 00:00:00.000 Win
B 2010-01-13 00:00:00.000 Lose
B 2010-01-19 00:00:00.000 Win
B 2010-01-25 00:00:00.000 Win
B 2010-01-31 00:00:00.000 Win
B 2010-02-06 00:00:00.000 Win
B 2010-02-12 00:00:00.000 Lose
B 2010-02-18 00:00:00.000 Lose
C 2010-01-01 00:00:00.000 Lose
C 2010-01-07 00:00:00.000 Lose
C 2010-01-13 00:00:00.000 Lose
C 2010-01-19 00:00:00.000 Lose
C 2010-01-25 00:00:00.000 Lose
C 2010-01-31 00:00:00.000 Win
C 2010-02-06 00:00:00.000 Win
C 2010-02-12 00:00:00.000 Lose
C 2010-02-18 00:00:00.000 Win

find the biggest number of consecutive wins or loses regardless of the team.

Here is the code that creates our team table:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
CREATE TABLE team ( 
  Team   CHAR(1), 
  DATE   DATETIME, 
  WiLose VARCHAR(10)) 
 
SET DATEFORMAT  dmy 
 
INSERT INTO team 
VALUES     ('A','01/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('A','07/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','13/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','19/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','25/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','31/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','06/02/2010','Win') 
 
INSERT INTO team 
VALUES     ('A','12/02/2010','Lose') 
 
INSERT INTO team 
VALUES     ('A','18/02/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','01/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('B','07/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','13/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('B','19/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','25/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','31/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','06/02/2010','Win') 
 
INSERT INTO team 
VALUES     ('B','12/02/2010','Lose') 
 
INSERT INTO team 
VALUES     ('B','18/02/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','01/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','07/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','13/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','19/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','25/01/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','31/01/2010','Win') 
 
INSERT INTO team 
VALUES     ('C','06/02/2010','Win') 
 
INSERT INTO team 
VALUES     ('C','12/02/2010','Lose') 
 
INSERT INTO team 
VALUES     ('C','18/02/2010','Win') 
 
SET DATEFORMAT  mdy 
 
SELECT * 
FROM   team
CREATE TABLE team ( 
  Team   CHAR(1), 
  DATE   DATETIME, 
  WiLose VARCHAR(10)) 

SET DATEFORMAT  dmy 

INSERT INTO team 
VALUES     ('A','01/01/2010','Win') 

INSERT INTO team 
VALUES     ('A','07/01/2010','Lose') 

INSERT INTO team 
VALUES     ('A','13/01/2010','Lose') 

INSERT INTO team 
VALUES     ('A','19/01/2010','Lose') 

INSERT INTO team 
VALUES     ('A','25/01/2010','Lose') 

INSERT INTO team 
VALUES     ('A','31/01/2010','Lose') 

INSERT INTO team 
VALUES     ('A','06/02/2010','Win') 

INSERT INTO team 
VALUES     ('A','12/02/2010','Lose') 

INSERT INTO team 
VALUES     ('A','18/02/2010','Win') 

INSERT INTO team 
VALUES     ('B','01/01/2010','Lose') 

INSERT INTO team 
VALUES     ('B','07/01/2010','Win') 

INSERT INTO team 
VALUES     ('B','13/01/2010','Lose') 

INSERT INTO team 
VALUES     ('B','19/01/2010','Win') 

INSERT INTO team 
VALUES     ('B','25/01/2010','Win') 

INSERT INTO team 
VALUES     ('B','31/01/2010','Win') 

INSERT INTO team 
VALUES     ('B','06/02/2010','Win') 

INSERT INTO team 
VALUES     ('B','12/02/2010','Lose') 

INSERT INTO team 
VALUES     ('B','18/02/2010','Lose') 

INSERT INTO team 
VALUES     ('C','01/01/2010','Lose') 

INSERT INTO team 
VALUES     ('C','07/01/2010','Lose') 

INSERT INTO team 
VALUES     ('C','13/01/2010','Lose') 

INSERT INTO team 
VALUES     ('C','19/01/2010','Lose') 

INSERT INTO team 
VALUES     ('C','25/01/2010','Lose') 

INSERT INTO team 
VALUES     ('C','31/01/2010','Win') 

INSERT INTO team 
VALUES     ('C','06/02/2010','Win') 

INSERT INTO team 
VALUES     ('C','12/02/2010','Lose') 

INSERT INTO team 
VALUES     ('C','18/02/2010','Win') 

SET DATEFORMAT  mdy 

SELECT * 
FROM   team

This problem is known as ‘finding islands of data’, and I always refer to this blog by Plamen Ratchev Refactoring Ranges when I am facing such scenario.

The idea of a solution is to first identify the blocks of consecutive wins or loses by each team. Applying the idea from Plamen’s blog first step will be

T-SQL
1
2
3
4
5
6
;WITH cte AS (SELECT *, 
ROW_NUMBER() OVER 
(
PARTITION BY team ORDER BY DATE
) - ROW_NUMBER() OVER (PARTITION BY team, wilose ORDER BY DATE) AS [GroupID]
FROM team),
;WITH cte AS (SELECT *, 
ROW_NUMBER() OVER 
(
PARTITION BY team ORDER BY DATE
) - ROW_NUMBER() OVER (PARTITION BY team, wilose ORDER BY DATE) AS [GroupID]
FROM team),

By assigning GroupID we identified blocks of winning or losing streaks.

The last two steps are simple enough:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Calculate Wins/Loses totals per each team/block
cte1 AS (SELECT team, 
SUM(CASE WHEN WiLose = 'Win' THEN 1 ELSE 0 END) AS [Wins],
SUM(CASE WHEN WiLose = 'Lose' THEN 1 ELSE 0 END) AS [Loses]
FROM cte
GROUP BY Team, GroupID),
 
-- Rank from highest to lowest wins / loses
cteSummary AS (SELECT *, RANK() OVER (ORDER BY Wins DESC) AS WinsRank,
RANK() OVER (ORDER BY Loses DESC) AS LosesRank
FROM cte1)
-- Get final results
SELECT Team, [Wins],[Loses]
 FROM cteSummary WHERE WinsRank = 1 OR LosesRank = 1
ORDER BY Team
-- Calculate Wins/Loses totals per each team/block
cte1 AS (SELECT team, 
SUM(CASE WHEN WiLose = 'Win' THEN 1 ELSE 0 END) AS [Wins],
SUM(CASE WHEN WiLose = 'Lose' THEN 1 ELSE 0 END) AS [Loses]
FROM cte
GROUP BY Team, GroupID),

-- Rank from highest to lowest wins / loses
cteSummary AS (SELECT *, RANK() OVER (ORDER BY Wins DESC) AS WinsRank,
RANK() OVER (ORDER BY Loses DESC) AS LosesRank
FROM cte1)
-- Get final results
SELECT Team, [Wins],[Loses]
 FROM cteSummary WHERE WinsRank = 1 OR LosesRank = 1
ORDER BY Team

So, correctly identifying a known pattern helps to solve such problems very quickly.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum