Peter Larsson Blog

Patron Saint of Lost Yaks

Finding streaks in data

by Peter Larsson in  , ,

A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data.
Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

And this is an alternative way to find your streaks

-- Prepare sample data
SET NOCOUNT ON
 
DECLARE       @GameResults TABLE
       (
              gameID INT,
              homeScore INT,
              awayScore INT
       )
 
INSERT @GameResults
SELECT 1, 2, 1 UNION ALL
SELECT 2, 4, 1 UNION ALL
SELECT 3, 4, 3
 
DECLARE       @Program TABLE
       (
              gameID INT,
              gameDate DATETIME,
              homeID INT,
              awayID INT
       )
 
INSERT @Program
SELECT 1, '2008-05-12', 101, 102 UNION ALL
SELECT 2, '2008-05-20', 106, 101 UNION ALL
SELECT 3, '2008-05-14', 107, 101
 
-- Prepare staging data
DECLARE       @Stage TABLE
       (
              teamID INT,
              gameDate DATETIME,
              outcome CHAR(3),
              streak INT,
              PRIMARY KEY CLUSTERED
              (
                     teamID,
                     gameDate
              )
       )
 
INSERT        @Stage
              (
                     teamID,
                     gameDate,
                     outcome
              )
SELECT        t.teamID,
              t.gameDate,
              CASE
                     WHEN t.homeScore = t.awayScore THEN 'tie'
                     WHEN t.theRoute = 'homeID' AND t.homeScore > t.awayScore THEN 'win'
                     WHEN t.theRoute = 'homeID' THEN 'los'
                     WHEN t.theRoute = 'awayID' AND t.homeScore < t.awayScore THEN 'win'
                     WHEN t.theRoute = 'awayID' THEN 'los'
              END AS outcome
FROM          (
                     SELECT        p.homeID,
                                  gr.homeScore,
                                  p.gameDate,
                                  p.awayID,
                                  gr.awayScore
                     FROM          @GameResults AS gr
                     INNER JOIN    @Program AS p ON p.gameID = gr.gameID
              ) AS g
UNPIVOT              (
                     teamID
                     FOR theRoute IN(g.homeID, g.awayID)
              ) AS t
ORDER BY      teamID,
              gameDate
 
-- Fix the streak numbering
DECLARE       @teamID INT,
       @outcome CHAR(3),
       @streak INT
 
SELECT TOP 1 @teamID = teamID,
              @outcome = outcome,
              @streak = 1
FROM          @Stage
ORDER BY      teamID,
              gameDate
 
UPDATE @Stage
SET    @streak = streak =   CASE
                                  WHEN teamID = @teamID AND @outcome = outcome THEN @streak
                                  ELSE @streak + 1
                           END,
       @teamID = teamID,
       @outcome = outcome
 
SELECT        teamID,
              outcome,
              COUNT(*) AS streaks,
              MIN(gameDate) AS fromDate,
              MAX(gameDate) AS toDate
FROM          @Stage
GROUP BY      teamID,
              outcome
ORDER BY      teamID,
              outcome,
              COUNT(*) DESC
teamID  outcome  streaks  fromDate    toDate
101     los      2        2008-05-14  2008-05-20
101     win      1        2008-05-12  2008-05-12
102     los      1        2008-05-12  2008-05-12
106     win      1        2008-05-20  2008-05-20
107     win      1        2008-05-14  2008-05-14

Legacy Comments
ansar
2008-11-27		 re: Finding streaks in data
I am interested to know more about sql.
Log Buffer
2008-11-28		 re: Finding streaks in data
"Thinking outside the box has an SQL recipe for finding streaks in data—as in, winning or losing streaks. Must be a Maple Leafs fan."

Log Buffer #125
Alex
2011-06-03		 re: Finding streaks in data
At last, someone comes up with the "right" aneswr!
ltaenl
2011-06-05		 re: Finding streaks in data
BlevsD , [url=http://girgrxhhsocx.com/]girgrxhhsocx[/url], [link=http://kqprsfujnnbh.com/]kqprsfujnnbh[/link], http://dflzqdrufcss.com/