SQL Puzzle - Consecutive Wins Solution

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:

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