Consecutive Ranks

     

Write an SQL query to identify all ranks that appear in a sequence of at least three consecutive occurrences.

Input :-

Scores table :-
ID is the Primary Key column for the Scores table.

ID	Ranks
1	1
2	2
3	2
4	2
5	1
6	1
7	2

Output :-

ConsecutiveRanks

2

Query :-

with scores_rank as
(  
    select Ranks, 
    lead(Ranks,1) over() Rank1,
    lead(Ranks,2) over() Rank2
    from Scores
)
select distinct Ranks as ConsecutiveRanks
from scores_rank
where Ranks=Rank1 and Ranks=Rank2;

Explanation : - 

The given query is written in SQL and uses a common table expression (CTE) to perform a specific task. Let's break down the query :-

1) The CTE (named "scores_rank") is defined first, which retrieves data from a table called "Scores". 

• It selects the Ranks column from the Scores table.
• The LEAD(Ranks,1) function is used to retrieve the next rank value after the current row and is named as column 'Rank1'.
• The LEAD(Ranks,2) function again to retrieve the rank value two rows ahead and is named as column 'Rank2'.

2) After defining the CTE(scores_rank), the main query selects distinct values from the ‘Ranks’ column of the CTE(scores_rank).

• The WHERE clause filters the results by only selecting rows where the 'Ranks' value is equal to both ‘Rank1’ and ‘Rank2’.

In summary, this query aims to find and display the consecutive ranks from the "Scores" table. It does so by using the LEAD() function to compare each rank with the next two ranks. If a rank appears consecutively (i.e., it is equal to the next two ranks) for atleast three times, it is included in the result set. The 'distinct' keyword ensures that only unique consecutive ranks are returned.