Here are the queries which I wrote to solve the SQL Puzzle - Consecutive Wins. Both queries has totally different approches, hope you like them.
Also here I want to mention that these solution here are just to solve the problem, and there could be many other ways to solve it in better way. So please try yourself too :)
Just rephrasing the problem statement again to give you context:
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.
Solution 1:
-------------------------------------------------------------------------------------------------
Solution 2:
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 - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers
Also here I want to mention that these solution here are just to solve the problem, and there could be many other ways to solve it in better way. So please try yourself too :)
Just rephrasing the problem statement again to give you context:
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.
Solution 1:
WITH t 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
),
mytable as
(
select
LISTAGG(wl, '') WITHIN GROUP (ORDER BY matchdate) TXT,
team
from
T
group by team
)
SELECT TEAM, len continious_wins FROM
(
select
mytable.TEAM,
LENGTH(regexp_substr ( mytable.txt, '[^L]+', 1, n)) as len,
max(LENGTH(regexp_substr ( mytable.txt, '[^L]+', 1, n))) over () mlen
from
mytable,
(
select
level n
from
(
select
max ( length (txt) - length (replace (txt, 'L'))) as max_commas
from
mytable
)
connect by level <= 1 + max_commas
) ctr
where
ctr.n <= 1 + length (txt) - length (replace (txt, 'L'))
)
where mlen = len
-------------------------------------------------------------------------------------------------
Solution 2:
WITH t 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('3-JAN-2012'),'L' 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
)
select team, cwin from
(
select
team,
cwin,
max(cwin) over (partition by team) mwin
from
(
select
team,
lead(wend) over (partition by team order by matchdate) - wstart + 1 cwin
from
(
select
row_number() over (partition by team order by matchdate) rn,
case when wl = 'W' and NVL(lag(wl) over (partition by team order by matchdate),'L') = 'L'
then row_number() over (partition by team order by matchdate) end wstart,
case when wl = 'W' and NVL(lead(wl) over (partition by team order by matchdate),'L') = 'L'
then row_number() over (partition by team order by matchdate) end wend,
team,
wl,
matchdate
from
t
)
where wstart is not null or wend is not null
)
) where cwin = mwin
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 - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers
This comment has been removed by the author.
ReplyDeleteWITH t AS
ReplyDelete(
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('3-JAN-2012'),'L' 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
)
select
MR1.Team,
rank() over (order by MR1.wincount desc, MR1.begin_dt desc) as Team_consecutive_wins_rank,
MR1.wincount,
MR1.begin_dt,
MR1.end_dt
from t
match_recognize (
PARTITION by team
ORDER by matchdate
MEASURES
strt.matchdate as begin_dt,
count(mwin.*)+1 as wincount,
last(mwin.matchdate) as end_dt
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (strt mwin*)
DEFINE
mwin as WL = 'W'
) MR1
order by 2 asc
;
gives output:
TEAM TEAM_CONSECUTIVE_WINS_RANK WINCOUNT BEGIN_DT END_DT
----- -------------------------- ---------- --------- ---------
TeamB 1 4 03-JAN-12 06-JAN-12
TeamA 2 3 07-JAN-12 10-JAN-12
TeamA 3 3 01-JAN-12 03-JAN-12