SQL Puzzle - Consecutive Wins

Many of my friends asked me to create a series of Questions/Puzzles for Oracle on my blog. This is my first attempt on this and I am hoping that you will also like it and will enjoy solving it.

Lets first create the table and data for this.

Step 1: Create a Table
SQL> create table team_stats
  2  (
  3    team_name   varchar2(100),
  4    match_date  date,
  5    result      char(1)
  6  );
Table created.

Step 2: Insert Data in table
SQL> insert into team_stats
  2  select 'TeamA' team ,to_date('1-JAN-2012') matchdate ,'W' WL from dual UNION ALL
  3  select 'TeamA', to_date('2-JAN-2012'),'W' WL from dual UNION ALL
  4  select 'TeamA', to_date('3-JAN-2012'),'W' WL from dual UNION ALL
  5  select 'TeamB', to_date('4-JAN-2012'),'W' WL from dual UNION ALL
  6  select 'TeamB', to_date('5-JAN-2012'),'W' WL from dual UNION ALL
  7  select 'TeamB', to_date('6-JAN-2012'),'W' WL from dual UNION ALL
  8  select 'TeamA', to_date('7-JAN-2012'),'L' WL from dual UNION ALL
  9  select 'TeamA', to_date('8-JAN-2012'),'W' WL from dual UNION ALL
 10  select 'TeamA', to_date('10-JAN-2012'),'W' WL from dual;
9 rows created.

SQL> commit;
Commit complete.

Lets view the data once before facing the problem
SQL> select * from team_stats order by match_date;
TEAM_NAME                              MATCH_DAT R
-------------------------------------- --------- -
TeamA                                  01-JAN-12 W
TeamA                                  02-JAN-12 W
TeamA                                  03-JAN-12 W
TeamB                                  04-JAN-12 W
TeamB                                  05-JAN-12 W
TeamB                                  06-JAN-12 W
TeamA                                  07-JAN-12 L
TeamA                                  08-JAN-12 W
TeamA                                  10-JAN-12 W
9 rows selected.

Now here is the problem statement:
We need to write a SQL Query (no PL/SQL please), that returns the team or teams that has/have the highest winning streak (consecutive wins) and the number of consecutive wins also.

You can put your answers on the comment box. Enjoy solving it :)

To view the solution of this puzzleClick Here.

More SQL Puzzles:
- SQL Puzzle - Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers

26 comments:

  1. select count(*),team_name from team_stats where result='W' and team_name='TeamA' group by team_name;

    ReplyDelete
    Replies
    1. Thanks for trying. But your solution incorrect, how you can select a team_name using team_name='TeamA' predicate.

      Delete
    2. SELECT team_name consecutive_wins_team, SUM (diff) AS consecutive_win_days
      FROM (SELECT *
      FROM (SELECT team_name,
      match_date,
      NVL (
      match_date
      - LAG(match_date)
      OVER (PARTITION BY team_name
      ORDER BY match_date),
      1
      )
      AS diff
      FROM team_stats
      WHERE result = 'W') t1
      WHERE diff = 1) t2
      GROUP BY team_name

      Delete
  2. comment received from "Milon Kanti Biswas"

    SELECT
    TEAM_NAME,
    MAX(STREAK) " highest winning streak",
    COUNT(STREAK) "number of consecutive wins" FROM
    (
    SELECT TEAM_NAME, STREAK
    FROM
    (
    SELECT TEAM_NAME,LTN,(LMD- MATCH_DATE)+1 STREAK FROM (
    SELECT * FROM (
    SELECT TEAM_NAME,LEAD(TEAM_NAME) OVER(ORDER BY MATCH_DATE) LTN,MATCH_DATE,MATCH_DATE+1 ADDMD,LEAD(MATCH_DATE) OVER(ORDER BY MATCH_DATE) LMD FROM(
    SELECT TEAM_NAME,MATCH_DATE FROM (
    SELECT TEAM_NAME,MATCH_DATE,LMD,LMD - MATCH_DATE DIFMD,MATCH_DATE-LGMD DIFLGMD,RESULT FROM(
    select TEAM_NAME,MATCH_DATE,LEAD(MATCH_DATE) OVER(ORDER BY TEAM_NAME,MATCH_DATE) LMD, LAG(MATCH_DATE) OVER(ORDER BY TEAM_NAME,MATCH_DATE) LGMD,RESULT FROM(
    select TEAM_NAME,MATCH_DATE,RESULT
    from TEAM_STATS
    where result<>'L'
    group by TEAM_NAME,MATCH_DATE,RESULT
    order by TEAM_NAME,MATCH_DATE)))
    WHERE DIFMD IS NULL OR DIFLGMD IS NULL OR (DIFMD<>1 AND DIFLGMD=1) OR (DIFMD=1 AND DIFLGMD<>1)
    ORDER BY MATCH_DATE)
    )
    WHERE ADDMD IN (SELECT MATCH_DATE FROM TEAM_STATS WHERE RESULT<>'L')))
    WHERE TEAM_NAME=LTN)
    GROUP BY TEAM_NAME
    ORDER BY TEAM_NAME;

    ReplyDelete
    Replies
    1. output is correct but is too complicated. try to simplify it :)

      Delete
    2. also if I delete one record if TeamB winning, still your query is showing both teams

      Delete
  3. Answer receive from unknown

    SELECT team_name consecutive_wins_team, SUM (diff) AS consecutive_win_days
    FROM (SELECT *
    FROM (SELECT team_name,
    match_date,
    NVL (
    match_date
    - LAG(match_date)
    OVER (PARTITION BY team_name
    ORDER BY match_date),
    1
    )
    AS diff
    FROM team_stats
    WHERE result = 'W') t1
    WHERE diff = 1) t2
    GROUP BY team_name

    ReplyDelete
    Replies
    1. if I delete one record if TeamB winning, still your query is showing both teams, it should only give 1 team with highest winning streak. but it is nice :)

      Delete
    2. MAY be this is wrong . inset following data into table and check .

      insert into team_stats values ('TeamA', to_date('25-JAN-2012'),'W');
      insert into team_stats values ('TeamA', to_date('26-JAN-2012'),'W');

      insert into team_stats values ('TeamA', to_date('29-JAN-2012'),'W');
      insert into team_stats values ('TeamA', to_date('30-JAN-2012'),'W');
      insert into team_stats values ('TeamA', to_date('31-JAN-2012'),'W');

      Delete
    3. I wont say it is wrong, it is just a step behind

      Delete
    4. SELECT *
      FROM ( SELECT team_name, (SUM (diff)) AS consecutive_win_days
      FROM (SELECT team_name,
      match_date,
      NVL (
      match_date
      - LAG (match_date)
      OVER (PARTITION BY team_name ORDER BY match_date),
      1)
      AS diff
      FROM team_stats
      WHERE result = 'W')
      WHERE diff = 1
      GROUP BY team_name
      ORDER BY 2 DESC)
      WHERE ROWNUM = 1

      Delete
  4. Received from "chandra sekar"
    select team_name "Consecutive wins "from team_stats where R= ( select count(R) from team_stats where R='W' );

    ReplyDelete
    Replies
    1. ERROR at line 1:
      ORA-00904: "R": invalid identifier

      Delete
  5. Replies
    1. if it is a exam your solution is partially correct. But this is surly not for production server.

      Delete
  6. select team_name "Consecutive wins" from team_stats where result= ( select count(Result) from team_stats where Result='W' );

    ReplyDelete
    Replies
    1. execute your query once before posing
      ORA-01722: invalid number

      Delete
  7. select team, streak_num
    from (
    SELECT team,
    count(*) streak_num,
    rank() OVER(ORDER BY count(*) DESC) rnk
    from (
    SELECT team, wl,
    row_number() OVER(PARTITION BY team ORDER BY wl, matchdate) -
    row_number() OVER(PARTITION BY team ORDER BY matchdate) grp
    FROM games
    )
    WHERE wl = 'W'
    GROUP BY team, grp
    )
    where rnk = 1;

    ReplyDelete
  8. SELECT TEAM_NAME,No_of_consecutive_wins FROM (
    SELECT TEAM_NAME,DENSE_RANK() OVER(ORDER BY No_of_consecutive_wins DESC) DRANK,No_of_consecutive_wins FROM (
    SELECT TEAM_NAME,SNO-LAG(SNO,1,0) OVER (ORDER BY SNO) No_of_consecutive_wins FROM(
    SELECT * FROM (
    SELECT A.*,ROWNUM SNO FROM (
    SELECT * FROM (
    SELECT case when instr(lead,team_name)=0 or lead_result='L' then sno end flg,lead,team_name,result from
    (SELECT TEMP.*,rownum sno FROM
    ( select lead(team_name,1,'X') over(order by TEAM_NAME,match_date) lead ,team_name ,result,
    lead(result,1,'X') over(order by TEAM_NAME,match_date) lead_result from team_stats)TEMP
    )
    ) WHERE RESULT<>'L') A
    ) WHERE FLG IS NOT NULL))) WHERE DRANK=1;

    ReplyDelete
  9. with ts as (
    SELECT row_number() over (partition by team_name order by match_date) rn, ts.*
    FROM TEAM_STATS ts
    where result = 'W'
    )
    select team_name, max(cnt) + 1 "highest winning streak",
    count(case when cnt = 1 then 1 else null end) no_cons
    from (
    select ts1.team_name, ts1.match_date, count(case when ts2.match_date - ts1.match_date = ts2.rn - ts1.rn then 1 else null end) cnt
    from ts ts1,
    ts ts2
    where ts1.team_name = ts2.team_name
    and ts1.rn <> ts2.rn
    and ts2.match_date - ts1.match_date > 0
    group by ts1.team_name, ts1.match_date
    )
    group by team_name

    ReplyDelete
  10. select team_name from (select team_name,result,count(*)as c from team_stats where result <>'L' group by team_name,result order by c desc)where rownum<2 ;

    ReplyDelete
  11. select * from ( select sum(case when a.result1=a.result then 1 else 0 end ) as consecutive_wins,a.team_name from (select nvl(lead(result,1) over(order by team_name,match_date),result) as result1, result , team_name from team_stats order by team_name)a group by a.team_name order by consecutive_wins desc) where rownum=1;

    ReplyDelete
  12. select team_name, Wins Continous_wins from (
    select rank() over (order by min(R) desc) Rank, team_name,min(R) Wins from (
    select dense_rank() OVER (partition by team_name ORDER BY team_name,result desc , NR desc, match_date) R, team_name,match_date,result,NR from (
    select team_name,match_date,result,
    nvl(LEAD(result, 1) OVER (partition by team_name ORDER BY team_name,match_date),'L') NR from team_stats
    order by team_name,match_date))
    where result='W' and NR='L' group by team_name order by min(R) desc)
    where Rank=1;

    ReplyDelete
  13. SELECT
    TEAM_NAME, SEQ_WIN, COUNT(*) SEQ_WIN_CNT
    FROM (
    SELECT TEAM_NAME, COUNT(*) SEQ_WIN, MAX(COUNT(*))OVER() MAX_CNT
    FROM (
    SELECT
    TEAM_NAME, MATCH_DATE, RESULT
    ,ROW_NUMBER()OVER(PARTITION BY TEAM_NAME ORDER BY MATCH_DATE)
    - ROW_NUMBER()OVER(PARTITION BY TEAM_NAME, RESULT ORDER BY MATCH_DATE) RN
    FROM TEAM_STATS)
    GROUP BY TEAM_NAME,RN)
    WHERE SEQ_WIN = MAX_CNT
    GROUP BY TEAM_NAME,SEQ_WIN

    ReplyDelete
  14. WITH E AS (SELECT id,status,status1
    FROM (
    SELECT teamid id, status,LEAD(status) OVER(PARTITION BY teamid ORDER BY date1) status1
    FROM teams)
    WHERE status=status1
    AND status='WON')
    SELECT e1.id,cnt1--,cnt2
    FROM
    (SELECT ID,COUNT(*) cnt1 FROM E GROUP BY ID) E1
    ,(SELECT MAX(COUNT(*)) cnt2 FROM E GROUP BY ID) E2
    WHERE E1.cnt1=E2.cnt2;

    ReplyDelete
  15. with team_win as (
    select distinct team_name,count(team_name) over(partition by team_name, win) max_wins from
    (select team_name, result,
    max(case when team_name <> lag_team then rn
    else (case when result = lag_result then decode(rn,1,1,null) else rn end ) end) over( order by rn asc) as win,rn
    from(
    select rownum rn, team_name, lag(team_name) over (order by match_date asc) as lag_team, result,
    lag(result) over (order by match_date asc) as lag_result
    from team_stats ts) ))

    select team_name, max_wins from team_win
    where max_wins = (select max(max_wins) from team_win);

    ReplyDelete