Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I made a SQL Fiddle for this question if that makes things easier for anyone.

I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data (like 'W2' if the team has won their last 2 matchups, or 'L1' if they lost their last matchup after winning the previous matchup - or 'T1' if they tied their most recent matchup).

Here is my basic schema:

CREATE TABLE FantasyTeams (
  team_id BIGINT NOT NULL
)

CREATE TABLE FantasyMatches(
    match_id BIGINT NOT NULL,
    home_fantasy_team_id BIGINT NOT NULL,
    away_fantasy_team_id BIGINT NOT NULL,
    fantasy_season_id BIGINT NOT NULL,
    fantasy_league_id BIGINT NOT NULL,
    fantasy_week_id BIGINT NOT NULL,
    winning_team_id BIGINT NULL
)

A value of NULL in the winning_team_id column indicates a tie for that match.

Here's a sample DML statement with some sample data for 6 teams and 3 weeks worth of matchups:

INSERT INTO FantasyTeams
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6

INSERT INTO FantasyMatches
SELECT 1, 2, 1, 2, 4, 44, 2
UNION
SELECT 2, 5, 4, 2, 4, 44, 5
UNION
SELECT 3, 6, 3, 2, 4, 44, 3
UNION
SELECT 4, 2, 4, 2, 4, 45, 2
UNION
SELECT 5, 3, 1, 2, 4, 45, 3
UNION
SELECT 6, 6, 5, 2, 4, 45, 6
UNION
SELECT 7, 2, 6, 2, 4, 46, 2
UNION
SELECT 8, 3, 5, 2, 4, 46, 3
UNION
SELECT 9, 4, 1, 2, 4, 46, NULL

GO

Here is an example of the desired output (based on the DML above) that I'm having trouble even beginning to figure out how to derive:

| TEAM_ID | STEAK_TYPE | STREAK_COUNT |
|---------|------------|--------------|
|       1 |          T |            1 |
|       2 |          W |            3 |
|       3 |          W |            3 |
|       4 |          T |            1 |
|       5 |          L |            2 |
|       6 |          L |            1 |

I've tried various methods using subqueries and CTE's but I can't put it together. I'd like to avoid using a cursor as I could have a large dataset to run this against in the future. I feel like there might be a way involving table variables that join this data to itself somehow but I'm still working on it.

Additional Info: There could be a varying number of teams (any even number between 6 and 10) and the total matchups will increase by 1 for each team every week. Any ideas on how I should do this?

share|improve this question
2  
Incidentally, all such schemas I've ever seen use a tristate (eg 1 2 3 meaning Home Win / Tie / Away Win) column for the match result, rather than your winning_team_id with value id / NULL / id. One less constraint for the DB to have to check. –  AakashM May 8 at 11:28
    
So are you saying the design I setup is "good" ? –  jamauss May 8 at 16:45
1  
Well, if I'm asked for comments I would say: 1) why 'fantasy' in so many names 2) why bigint for so many columns where int would probably do 3) why all the _s?! 4) I prefer table names to be singular but acknowledge not everyone agrees with me // but those aside what you've shown us here looks coherent, yes –  AakashM May 8 at 19:04
add comment

3 Answers 3

up vote 16 down vote accepted

Since you are on SQL Server 2012 you can use a couple of the new windowing functions.

with C1 as
(
  select T.team_id,
         case
           when M.winning_team_id is null then 'T'
           when M.winning_team_id = T.team_id then 'W'
           else 'L'
         end as streak_type,
         M.match_id
  from FantasyMatches as M
    cross apply (values(M.home_fantasy_team_id),
                       (M.away_fantasy_team_id)) as T(team_id)
), C2 as
(
  select C1.team_id,
         C1.streak_type,
         C1.match_id,
         lag(C1.streak_type, 1, C1.streak_type) 
           over(partition by C1.team_id 
                order by C1.match_id desc) as lag_streak_type
  from C1
), C3 as
(
  select C2.team_id,
         C2.streak_type,
         sum(case when C2.lag_streak_type = C2.streak_type then 0 else 1 end) 
           over(partition by C2.team_id 
                order by C2.match_id desc rows unbounded preceding) as streak_sum
  from C2
)
select C3.team_id,
       C3.streak_type,
       count(*) as streak_count
from C3
where C3.streak_sum = 0
group by C3.team_id,
         C3.streak_type
order by C3.team_id;

SQL Fiddle

C1 calculates the streak_type for each team and match.

C2 finds the previous streak_type ordered by match_id desc.

C3 generates a running sum streak_sum ordered by match_id desc keeping a 0 a long as the streak_type is the same as the last value.

Main query sums up the streaks where streak_sum is 0.

share|improve this answer
4  
+1 for the use of LEAD(). Not enough people know about the new windowing functions in 2012 –  Mark Sinkinson May 8 at 9:10
4  
+1, I like the trick of using the descending order in LAG to later determine the last streak, very neat! By the way, since the OP only wants team IDs, you could replace FantasyTeams JOIN FantasyMatches with FantasyMatches CROSS APPLY (VALUES (home_fantasy_team_id), (away_fantasy_team_id)) and thus potentially improve performance. –  Andriy M May 8 at 15:42
    
@AndriyM Good catch!! I will update the answer with that. If you need other columns from FantasyTeams it is probably better to join in the main query instead. –  Mikael Eriksson May 8 at 16:06
    
Thanks for this code example - I'm going to give this a try and will report back a little later after I'm out of meetings... >:-\ –  jamauss May 8 at 16:47
    
@MikaelEriksson - This works great - thanks! Quick question - I need to use this result set to update existing rows (joining on FantasyTeams.team_id) - How would you recommend turning this into an UPDATE statement? I started trying to just change the SELECT into an UPDATE but I can't use the GROUP BY in an UPDATE. Would you say I should just throw the result set into a temp table and join against that to UPDATE or something else? Thanks! –  jamauss May 8 at 18:11
show 2 more comments

Another way to get the result is by a recursive CTE

WITH TeamRes As (
SELECT FT.Team_ID
     , FM.match_id
     , Previous_Match = LAG(match_id, 1, 0) 
                        OVER (PARTITION BY FT.Team_ID ORDER BY FM.match_id)
     , Matches = Row_Number() 
                 OVER (PARTITION BY FT.Team_ID ORDER BY FM.match_id Desc)
     , Result = Case Coalesce(winning_team_id, -1)
                     When -1 Then 'T'
                     When FT.Team_ID Then 'W'
                     Else 'L'
                End 
FROM   FantasyMatches FM
       INNER JOIN FantasyTeams FT ON FT.Team_ID IN 
         (FM.home_fantasy_team_id, FM.away_fantasy_team_id)
), Streaks AS (
SELECT Team_ID, Result, 1 As Streak, Previous_Match
FROM   TeamRes
WHERE  Matches = 1
UNION ALL
SELECT tr.Team_ID, tr.Result, Streak + 1, tr.Previous_Match
FROM   TeamRes tr
       INNER JOIN Streaks s ON tr.Team_ID = s.Team_ID 
                           AND tr.Match_id = s.Previous_Match 
                           AND tr.Result = s.Result
)
Select Team_ID, Result, Max(Streak) Streak
From   Streaks
Group By Team_ID, Result
Order By Team_ID

SQLFiddle demo

share|improve this answer
    
thanks for this answer, it's nice to see more than one solution to the problem and be able to compare performance between the two. –  jamauss May 8 at 18:12
add comment

One intuitive approach to solve this problem is:

  1. Find the most recent result for each team
  2. Check the previous match and add one to the streak count if the result type matches
  3. Repeat step 2 but stop as soon as the first different result is encountered

This strategy might win out over the window function solution (which performs a full scan of the data) as the table grows larger, assuming the recursive strategy is implemented efficiently. The key to success is to provide efficient indexes to locate rows quickly (using seeks) and to avoid sorts. The indexes needed are:

-- New index #1
CREATE UNIQUE INDEX uq1 ON dbo.FantasyMatches 
    (home_fantasy_team_id, match_id) 
INCLUDE (winning_team_id);

-- New index #2
CREATE UNIQUE INDEX uq2 ON dbo.FantasyMatches 
    (away_fantasy_team_id, match_id) 
INCLUDE (winning_team_id);

To assist in query optimization, I will use a temporary table to hold rows identified as forming part of a current streak. If streaks are typically short (as is true for the teams I follow, sadly) this table should be quite small:

-- Table to hold just the rows that form streaks
CREATE TABLE #StreakData
(
    team_id bigint NOT NULL,
    match_id bigint NOT NULL,
    streak_type char(1) NOT NULL,
    streak_length integer NOT NULL,
);

-- Temporary table unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq ON #StreakData (team_id, match_id);

My recursive query solution is as follows (SQL Fiddle here):

-- Solution query
WITH Streaks AS
(
    -- Anchor: most recent match for each team
    SELECT 
        FT.team_id, 
        CA.match_id, 
        CA.streak_type, 
        streak_length = 1
    FROM dbo.FantasyTeams AS FT
    CROSS APPLY
    (
        -- Most recent match
        SELECT
            T.match_id,
            T.streak_type
        FROM 
        (
            SELECT 
                FM.match_id, 
                streak_type =
                    CASE 
                        WHEN FM.winning_team_id = FM.home_fantasy_team_id
                            THEN CONVERT(char(1), 'W')
                        WHEN FM.winning_team_id IS NULL
                            THEN CONVERT(char(1), 'T')
                        ELSE CONVERT(char(1), 'L')
                    END
            FROM dbo.FantasyMatches AS FM
            WHERE 
                FT.team_id = FM.home_fantasy_team_id
            UNION ALL
            SELECT 
                FM.match_id, 
                streak_type =
                    CASE 
                        WHEN FM.winning_team_id = FM.away_fantasy_team_id
                            THEN CONVERT(char(1), 'W')
                        WHEN FM.winning_team_id IS NULL
                            THEN CONVERT(char(1), 'T')
                        ELSE CONVERT(char(1), 'L')
                    END
            FROM dbo.FantasyMatches AS FM
            WHERE
                FT.team_id = FM.away_fantasy_team_id
        ) AS T
        ORDER BY 
            T.match_id DESC
            OFFSET 0 ROWS 
            FETCH FIRST 1 ROW ONLY
    ) AS CA
    UNION ALL
    -- Recursive part: prior match with the same streak type
    SELECT 
        Streaks.team_id, 
        LastMatch.match_id, 
        Streaks.streak_type, 
        Streaks.streak_length + 1
    FROM Streaks
    CROSS APPLY
    (
        -- Most recent prior match
        SELECT 
            Numbered.match_id, 
            Numbered.winning_team_id, 
            Numbered.team_id
        FROM
        (
            -- Assign a row number
            SELECT
                PreviousMatches.match_id,
                PreviousMatches.winning_team_id,
                PreviousMatches.team_id, 
                rn = ROW_NUMBER() OVER (
                    ORDER BY PreviousMatches.match_id DESC)
            FROM
            (
                -- Prior match as home or away team
                SELECT 
                    FM.match_id, 
                    FM.winning_team_id, 
                    team_id = FM.home_fantasy_team_id
                FROM dbo.FantasyMatches AS FM
                WHERE 
                    FM.home_fantasy_team_id = Streaks.team_id
                    AND FM.match_id < Streaks.match_id
                UNION ALL
                SELECT 
                    FM.match_id, 
                    FM.winning_team_id, 
                    team_id = FM.away_fantasy_team_id
                FROM dbo.FantasyMatches AS FM
                WHERE 
                    FM.away_fantasy_team_id = Streaks.team_id
                    AND FM.match_id < Streaks.match_id
            ) AS PreviousMatches
        ) AS Numbered
        -- Most recent
        WHERE 
            Numbered.rn = 1
    ) AS LastMatch
    -- Check the streak type matches
    WHERE EXISTS
    (
        SELECT 
            Streaks.streak_type
        INTERSECT
        SELECT 
            CASE 
                WHEN LastMatch.winning_team_id IS NULL THEN 'T' 
                WHEN LastMatch.winning_team_id = LastMatch.team_id THEN 'W' 
                ELSE 'L' 
            END
    )
)
INSERT #StreakData
    (team_id, match_id, streak_type, streak_length)
SELECT
    team_id,
    match_id,
    streak_type,
    streak_length
FROM Streaks
OPTION (MAXRECURSION 0);

The T-SQL text is quite long, but each section of the query corresponds closely to the broad process outline given at the start of this answer. The query is made longer by the need to use certain tricks to avoids sorts and to produce a TOP in the recursive part of the query (which is normally not allowed).

The execution plan is relatively small and simple by comparison with the query. I have shaded the anchor region yellow, and the recursive part green in the screenshot below:

Recursive execution plan

With the streak rows captured in a temporary table, it is easy to get the summary results you require. (Using a temporary table also avoids a sorting spill that might occur if the query below were combined with the main recursive query)

-- Basic results
SELECT
    SD.team_id,
    StreakType = MAX(SD.streak_type),
    StreakLength = MAX(SD.streak_length)
FROM #StreakData AS SD
GROUP BY 
    SD.team_id
ORDER BY
    SD.team_id;

Basic query execution plan

The same query can be used as a basis for updating the FantasyTeams table:

-- Update team summary
WITH StreakData AS
(
    SELECT
        SD.team_id,
        StreakType = MAX(SD.streak_type),
        StreakLength = MAX(SD.streak_length)
    FROM #StreakData AS SD
    GROUP BY 
        SD.team_id
)
UPDATE FT
SET streak_type = SD.StreakType,
    streak_count = SD.StreakLength
FROM StreakData AS SD
JOIN dbo.FantasyTeams AS FT
    ON FT.team_id = SD.team_id;

Or, if you prefer MERGE:

MERGE dbo.FantasyTeams AS FT
USING
(
    SELECT
        SD.team_id,
        StreakType = MAX(SD.streak_type),
        StreakLength = MAX(SD.streak_length)
    FROM #StreakData AS SD
    GROUP BY 
        SD.team_id
) AS StreakData
    ON StreakData.team_id = FT.team_id
WHEN MATCHED THEN UPDATE SET
    FT.streak_type = StreakData.StreakType,
    FT.streak_count = StreakData.StreakLength;

Either approach produces an efficient execution plan (based on the known number of rows in the temporary table):

Update execution plan

Finally, because the recursive method naturally includes the match_id in its processing, it is easy to add a list of the match_ids that form each streak to the output:

SELECT
    S.team_id,
    streak_type = MAX(S.streak_type),
    match_id_list =
        STUFF(
        (
            SELECT ',' + CONVERT(varchar(11), S2.match_id)
            FROM #StreakData AS S2
            WHERE S2.team_id = S.team_id
            ORDER BY S2.match_id DESC
            FOR XML PATH ('')
        ), 1, 1, ''),
    streak_length = MAX(S.streak_length)
FROM #StreakData AS S
GROUP BY 
    S.team_id
ORDER BY
    S.team_id;

Output:

Match list included

Execution plan:

Match list execution plan

share|improve this answer
    
This is a really amazing thorough in-depth answer Paul, thanks! And thanks for the tips on indexing and the addition of the match_id list. I'm thinking maybe I could implement this as a table-valued function for re-use whenever I need this kind of data. –  jamauss May 9 at 20:38
1  
Impressive! Is there a particular reason why your recursive part's WHERE is using EXISTS (... INTERSECT ...) instead of just Streaks.streak_type = CASE ...? I know the former method can be useful when you need to match NULLs on both sides as well as values but it's not as if the right part could produce any NULLs in this case, so... –  Andriy M May 12 at 6:19
1  
@AndriyM Yes there is. The code is very carefully written in a number of places and ways to produce a plan with no sorts. When CASE is used, the optimizer is unable to use a merge concatenation (which preserves union key order) and uses a concatenation plus sorts instead. –  Paul White May 12 at 6:37
    
Thank you for the clarification. I'm beginning to wish I hadn't upvoted your answer 20 min ago, because upvoting it now would have given me much greater satisfaction. –  Andriy M May 12 at 6:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.