CODEWITHSUNDEEP
sqlpostgresql
SDS
SDS

Reputation: 331

Select Consecutive Year Data using SQL

I have a table called <Employee_Bonus_Table> which structure is as below:

Id Year Bonus
1 2017 10000
1 2018 10000
1 2019 20000
1 2020 20000
2 2017 10000
2 2018 10000
7 2013 10000
7 2015 20000
7 2017 10000
8 2010 80000
8 2011 70000
8 2012 80000

I want to select , and for those Employee who have received Bonus at least for 3 consecutive years. So, the output should look like as below:

Id Year Bonus
1 2017 10000
1 2018 10000
1 2019 20000
1 2020 20000
8 2010 80000
8 2011 70000
8 2012 80000

Upvotes: 2

Views: 740

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a type of gaps-and-islands problem. The key observation is that if you subtract an enumerated sequence from the year, the value is constant for consecutive years.

Then you can count within each group and return groups with three or more rows:

select id, year, bonus
from (select t.*,
             count(*) over (partition by id, year - seqnum) as cnt_group
      from (select t.*,
                   row_number() over (partition by id order by year) as seqnum
            from t
           ) t
     ) t
where cnt_group >= 3
order by id, year;

Upvotes: 4

Related Questions