1 2 Previous Next 22 Replies Latest reply: Apr 4, 2013 8:23 AM by Nimish Garg RSS

    SQL Challenge

    popovitsj
      My teacher presented us with the following challenge.

      There is a table that has 3 columns:
      - Team
      - Date of the match
      - Won or Lost

      Write an SQL Query (no PL/SQL), that returns the team or teams that has/have the highest winning streak and the number of consecutive wins.

      Only 'regular' SQL is allowed, so no Oracle-specific functions.

      I have already (I think) solved this, but I was wondering what solutions you guys would come up with.

      I'll post my solution later.

      Below I've provided some sample DDL.
      CREATE TABLE Games(Team VARCHAR2(10), MatchDate DATE, WL VARCHAR2(1));
      INSERT INTO Games VALUES ('TeamA','1-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamA','2-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamA','3-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamB','4-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamB','5-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamB','6-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamA','7-JAN-2012','L');
      INSERT INTO Games VALUES ('TeamA','8-JAN-2012','W');
      INSERT INTO Games VALUES ('TeamA','10-JAN-2012','W');
      Edited by: popovitsj on Apr 3, 2013 7:50 AM

      Edited by: popovitsj on Apr 3, 2013 8:21 AM

      Edited by: popovitsj on 3-apr-2013 12:23

      Edited by: popovitsj on 3-apr-2013 12:36
        • 1. Re: SQL Challenge
          SomeoneElse
          MatchDate NUMBER(8,0)
          You lost me when you defined a date as a number.

          According to your data, TeamA had a win and a loss on the same day
          INSERT INTO Games VALUES ('TeamA',3,'W');
          INSERT INTO Games VALUES ('TeamA',3,'L');
          If that's correct, how can we know which occurred first?
          • 2. Re: SQL Challenge
            jeneesh
            To add - what is 'regular sql' ?
            • 3. Re: SQL Challenge
              popovitsj
              I defined date as a number to simplify the test data.

              I don't think that matters.

              You are right that you shouldnt have wins and losses on the same day for the same team. Sorry about that. I edited the sample data to fix that.

              Edited by: popovitsj on Apr 3, 2013 7:50 AM
              • 4. Re: SQL Challenge
                popovitsj
                Only regular sql means no database specific date-functions and such, but he didn't elaborate on it.
                • 5. Re: SQL Challenge
                  Frank Kulash
                  Hi,
                  popovitsj wrote:
                  I defined date as a number to simplify the test data.

                  I don't think that matters.
                  It always wrong to use a NUMBER for date information. Use a DATE, or maybe TIMESTAMP, instead.
                  If all you care about is putting the matches in order, then call the column something like match<b>seq</b>, not match<b>date</b>.

                  A Fixed-Difference approach can do this. See {message:id=9953384} and/or {message:id=9957164}

                  What if there happens to be a tie? For example, in your sample data, it looks like the longest winning streak is 3 games, and two different teams have achieved that. Do you want to see both 'TeamA' and 'TeamB' in the results, or do you just need to see one or the other? You said you wanted "the team or teams", which sounds like you want both, but it makes the solution more complicated, so I want to be sure.
                  • 6. Re: SQL Challenge
                    LostInPermuation
                    popovitsj wrote:
                    I defined date as a number to simplify the test data.

                    I don't think that matters.

                    You are right that you shouldnt have wins and losses on the same day for the same team. Sorry about that.
                    It does matter. It changes how data is queried, how it is handled...it changes everything. It changes how those that are trying to help you handle your request.

                    If you are requesting help, I would suggest that when asked to clarify an issue, not to basically reply "don't worry about it."
                    • 7. Re: SQL Challenge
                      popovitsj
                      My apologies. I have now fixed the sample data.
                      • 8. Re: SQL Challenge
                        Frank Kulash
                        Hi,

                        Using the Fixed Difference technique, I did this in 30 lines of code (I have a pretty verbose style), and 2 sub-queries.

                        That was for the solution the shows both 'TeamA' and 'TeamB', since they tied for the longest winning streak.
                        If you just need to see that the longest winning streak was 3 games, and one of the teams that did it, I did that in 20 lines and 1 sub-query, but I used the LAST function, and it's unclear if you're allowed to use that.
                        popovitsj wrote:
                        I have already (I think) solved this, but I was wondering what solutions you guys would come up with.

                        I'll post my solution later.
                        Right back at you.
                        • 9. Re: SQL Challenge
                          popovitsj
                          You're saying you want me to post first? :p
                          • 10. Re: SQL Challenge
                            Frank Kulash
                            Hi,
                            popovitsj wrote:
                            You're saying you want me to post first? :p
                            I'll wait a few days; hopefully, the assignment will be due by then.

                            However, you have the links that explain the fixed difference technique; you can get the same solution, or a better version, yourself.
                            • 11. Re: SQL Challenge
                              LostInPermuation
                              I believe that this gives you what you are looking for....
                              WITH wins
                                   AS (  SELECT *
                                           FROM games
                                          WHERE WL = 'W'
                                       ORDER BY MatchDate DESC)
                                SELECT team, COUNT (*) WL
                                  FROM wins
                              GROUP BY team
                              ;
                              giving output of
                              TEAM         WL
                              TeamA         5
                              TeamB         3
                              • 12. Re: SQL Challenge
                                popovitsj
                                LostInPermuation wrote:
                                I believe that this gives you what you are looking for....
                                WITH wins
                                AS (  SELECT *
                                FROM games
                                WHERE WL = 'W'
                                ORDER BY MatchDate DESC)
                                SELECT team, COUNT (*) WL
                                FROM wins
                                GROUP BY team
                                ;
                                giving output of
                                TEAM         WL
                                TeamA         5
                                TeamB         3
                                This is not correct. TeamA has a highest Winning Streak of 3, not 5.

                                Edited by: popovitsj on 3-apr-2013 12:07
                                • 13. Re: SQL Challenge
                                  popovitsj
                                  Below is my solution.

                                  I would like to hear if you guys think this is correct.
                                  WITH t1 AS
                                  (
                                       SELECT RowNum AS T1RowNum, Team, MatchDate, WL
                                       FROM Games
                                       ORDER BY Team, MatchDate
                                  ),
                                  t2 AS
                                  (
                                       SELECT t1a.Team, t1a.MatchDate, t1a.WL,
                                       Team ||
                                       (
                                            SELECT MIN(MatchDate)
                                            FROM t1 t1b
                                            WHERE t1a.Team = t1b.Team
                                            AND WL = 'W'
                                            AND t1b.T1RowNum < t1a.T1RowNum + 1
                                            AND t1b.T1RowNum >
                                            COALESCE
                                            (
                                                 (
                                                      SELECT MAX(T1RowNum)
                                                      FROM t1
                                                      WHERE Team = t1a.Team
                                                      AND T1RowNum < t1a.T1RowNum
                                                      AND WL = 'L'
                                                 ),
                                                 (
                                                      SELECT MIN(T1RowNum) - 1
                                                      FROM t1
                                                      WHERE Team = t1a.Team
                                                      AND T1RowNum < t1a.T1RowNum + 1
                                                 )
                                            )
                                       ) AS StreakTag
                                       FROM t1 t1a
                                       WHERE t1a.WL = 'W'
                                  ), t3 AS
                                  (
                                       SELECT StreakTag, Team, COUNT(*) AS Cnt
                                       FROM t2
                                       GROUP BY StreakTag, Team
                                  )
                                  SELECT Team, Cnt FROM t3
                                  WHERE Cnt = 
                                  (
                                       SELECT MAX(Cnt) FROM t3
                                  );
                                  • 14. Re: SQL Challenge
                                    Peter vd Zwan
                                    OK,

                                    I'll post first:
                                    WITH A AS
                                    (select 'TeamA' team ,to_date('1-JAN-2012') matchdate ,'W' WL from dual UNION ALL
                                    select 'TeamA', to_date('2-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamA', to_date('3-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamB', to_date('4-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamB', to_date('5-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamB', to_date('6-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamA', to_date('7-JAN-2012'),'L' WL from dual UNION ALL
                                    select 'TeamA', to_date('8-JAN-2012'),'W' WL from dual UNION ALL
                                    select 'TeamA', to_date('10-JAN-2012'),'W' WL from dual
                                    )
                                    ,B AS
                                    (
                                    SELECT
                                      TEAM
                                      ,COUNT( TEAM) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) CNT
                                      ,WL
                                      ,CASE WHEN WL = 'W' THEN 0 ELSE COUNT( TEAM) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) END L
                                      ,MATCHDATE
                                    FROM
                                      A
                                    )
                                    ,C AS
                                    (SELECT
                                      TEAM
                                      ,CNT - MAX(L) OVER (PARTITION BY TEAM ORDER BY MATCHDATE) ST
                                    FROM
                                      B
                                    )
                                    ,D AS
                                    (SELECT
                                      TEAM
                                      ,ST
                                      ,MAX(ST) OVER (PARTITION BY 1) LST
                                    FROM
                                      C
                                    )
                                    SELECT
                                      TEAM
                                      ,LST
                                    
                                    FROM
                                      D
                                    WHERE
                                      ST = LST
                                    
                                    TEAM  LST
                                    ----- ---
                                    TeamA   3 
                                    TeamB   3 
                                    Regards,

                                    Peter
                                    1 2 Previous Next