Grouping And Counting

I have a data set like this -- **Team Date W/L** Team_1 04/01/0012 W Team_1 06/01/0012 W Team_1 07/01/0012 L Team_1 14/01/0012 W Team_1 19/01/0012 W Team_1 30/01/001

Solution 1:

You can use the following:

SELECT  Team, TotalWins, FirstWin, LastWin
FROM    (   SELECT  Team, 
                    WL,
                    COUNT(*) TotalWins,
                    MIN("Date") FirstWin,
                    MAX("Date") LastWin,
                    ROW_NUMBER() OVER(PARTITIONBY Team, WL ORDERBYCOUNT(*) DESC) RowNumber
            FROM    (   SELECT  Team,
                                "Date",
                                WL, 
                                ROW_NUMBER() OVER(PARTITIONBY Team ORDERBY "Date") -ROW_NUMBER() OVER(PARTITIONBY Team, WL ORDERBY "Date") GroupingFROM    T
                    ) GroupedData
            WHERE   WL ='W'GROUPBY Team, WL, Grouping
        ) RankedData
WHERE   RowNumber =1;

It uses ROW_NUMBER to rank each game partitioned by team, and also by result, the difference between these two is unique for each group of consecutive results. So for your first team you would have:

TeamDate        W/L RN1RN2DIFFTeam_1  04/01/0012  W   110Team_1  06/01/0012  W   220Team_1  07/01/0012  L   312Team_1  14/01/0012  W   431Team_1  19/01/0012  W   541Team_1  30/01/0012  L   624Team_1  14/02/0012  W   752Team_1  17/02/0012  L   835Team_1  20/02/0012  W   963

Where RN1 is just partitioned by team, and rn2 is partition by team and result.

As you can see, if You remove the Losses then the DIFF column increments by one for each group of consecutive victories:

Team    Date        W/L RN1     RN2 DIFF
Team_1  04/01/0012  W   110
Team_1  06/01/0012  W   220---------------------------------------
Team_1  14/01/0012  W   431
Team_1  19/01/0012  W   541---------------------------------------
Team_1  14/02/0012  W   752---------------------------------------
Team_1  20/02/0012  W   963

You can then group by this to ensure you are looking at consecutive wins, and do a count to get the most. I've then just used another rownumber to get the maximum consecutive wins per team.

Example on SQL Fiddle