Search code examples
sqlanalytical

SQL - Row Sequencing

I have a table play day with following columns date_played, winner, loser with following values,

(Jun-03-14, USA, China)
(Jun-05-14, USA, Russia)
(Jun-06-14, France, Germany)
.
.
.
.
(Jun-09-14, USA, Russia)

I need to obtain all instances where USA has won exactly 3 rows in a sequence.

I tried with the following query.

Select 
    date, winner, loser, 
    RANK() OVER (PARTITION BY winner ORDER BY date rows 2 preceding) as rank 
from playday;
Solution

  • You can use the following query.

    select winner,loser,date,cnt from (select winner, loser, date, date - lag(date,3) over ( order by date) as cnt  from playday) where cnt >=3