Get records that have exactly (n) records in a row continuously

Question

Please consider this records:

Name	Year	Season
Name1	2011	1
Name1	2011	2
Name2	2012	1
Name2	2012	2
Name2	2012	3
Name2	2012	4
Name3	2012	3
Name3	2012	4
Name4	2012	1
Name4	2012	4
Name5	2012	3
Name5	2012	4
Name5	2013	1
Name5	2013	2

I want to get records that have 4 record in 4 seasons in a row. For example for above records I want to get Name2 and Name5 because Name2 has 4 records in each season in 2012 and Name5 has 4 records for 4 seasons in a row (for years 2012 and 2013). I tried to partition these records with Row_Number but it failed when we have 4 records in different years.

How can I achieve my desire result?

If I have below records for Name3:

Name	Year	Season
...
Name3	2014	1
Name3	2014	2
...

then Name3 wouldn't add to result because it hasn't 4 records for 4 seasons continuously.

Answer 1

Assuming there are no duplicates:

• You can assign a code to each row by multiplying the Year by 4 then adding the Season.
• Then get the row 3 previous to this using LAG.
• Then aggregate, and ensure using a HAVING clause that there is at least one row where the row 3 previous to it is exactly 3 seasons earlier.

SELECT
  Name
FROM (
    SELECT *,
      SeasonCode = Year * 4 + Season,
      Prev4SeasonCode = LAG(Year * 4 + Season, 3) OVER (PARTITION BY Name ORDER BY Year, Season)
    FROM YourTable t
) t
GROUP BY
  Name
HAVING COUNT(CASE WHEN Prev4SeasonCode = SeasonCode - 3 THEN 1 END) > 0;

db<>fiddle

Instead of using a SeasonCode, you could also do multiple conditions on Year and Season.