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 puzzle: Click 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:

UnknownDecember 26, 2013 at 5:05 PM
select count(*),team_name from team_stats where result='W' and team_name='TeamA' group by team_name;
ReplyDelete
Replies
Nimish GargDecember 27, 2013 at 12:07 PM
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
Nimish GargDecember 27, 2013 at 12:12 PM
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
Nimish GargDecember 27, 2013 at 12:16 PM
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
Milon Kanti BiswasDecember 27, 2013 at 12:24 PM
Brother, am i right or wrong?
ReplyDelete
Replies
Chandrasekar SDecember 27, 2013 at 12:50 PM
select team_name "Consecutive wins" from team_stats where result= ( select count(Result) from team_stats where Result='W' );
ReplyDelete
Replies
JamjeDecember 27, 2013 at 7:43 PM
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
Replies
UnknownJanuary 1, 2014 at 1:23 AM
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
Replies
viswa etikalaJanuary 27, 2014 at 9:19 AM
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
Replies
UnknownFebruary 28, 2014 at 11:15 PM
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
Replies
AnonymousMarch 10, 2015 at 2:17 AM
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
Replies
UnknownMay 3, 2017 at 1:51 AM
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
Replies
KATAYAMA NAOTOJuly 28, 2017 at 7:53 PM
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
Replies
VivekAugust 2, 2017 at 10:22 AM
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
Replies
UnknownSeptember 24, 2018 at 3:44 PM
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
Replies

Add comment