Problem is to find the
longest period of an event in an event log. Consider database for international
cricket teams which contains the log of their performance over the years. We
need to find longest winning streak for all teams using T-SQL.
We have a table MatchResults with following information Team Name, Match
date, Result( W for Win, L for Lost)
ID
|
Team
|
Result
|
Date
|
1
|
India
|
W
|
12/10/2013
|
2
|
England
|
L
|
12/15/2013
|
3
|
India
|
L
|
12/18/2013
|
4
|
India
|
W
|
12/30/2013
|
5
|
England
|
W
|
1/7/2014
|
6
|
India
|
W
|
1/14/2014
|
7
|
Australia
|
L
|
1/27/2014
|
8
|
India
|
W
|
2/12/2014
|
9
|
England
|
L
|
2/18/2014
|
10
|
India
|
W
|
2/25/2014
|
11
|
England
|
W
|
3/9/2014
|
12
|
England
|
W
|
3/11/2014
|
13
|
England
|
W
|
3/13/2014
|
14
|
Australia
|
W
|
3/21/2014
|
15
|
India
|
W
|
3/24/2014
|
We need to find the longest winning
streak for each team form above data. Following is expected result.
Team
|
Wins
|
Australia
|
1
|
England
|
3
|
India
|
5
|
T-SQL:
;WITH R AS
( SELECT Date,
Team,
Result
,
ROW_NUMBER() OVER (PARTITION BY Team ORDER BY Date) AS Seq
FROM
dbo.MatchResults
),
T AS
( SELECT Date,
Team,
RESULT,
Seq,
0 AS rec,
CASE WHEN RESULT = 'L' THEN 0 ELSE 1 END AS wincount
FROM R
WHERE
Seq = 1
UNION ALL
SELECT
R.Date,
R.Team,
R.RESULT,
r.seq
,
t.rec
+ 1 AS rec,
CASE WHEN r.RESULT = 'L' THEN 0 ELSE t.wincount + 1 END AS wincount
FROM R
INNER JOIN T ON (T.Seq = R.Seq -1 AND t.Team = R.Team)
)
SELECT
Team,
MAX(wincount) Wins
FROM T
GROUP BY Team
