SQL SELECT 3 consecutive records with the same value

Question

I want to select 3 consecutive (by year) records with same value from 'participation' table (name, year) :

Name    Year
-------------
Carol   1999
Carol   2000
Carol   2001
Carol   2002
Faith   1996
John    2001
John    2002
John    2003
John    2009
Lyla    1994
Lyla    1996
Lyla    1997

Here is my initial code :

SELECT DISTINCT p1.name, p1.year
FROM participatition p1,
     participatition p2
WHERE (p1.year = p2.year + 1 OR p1.year = p2.year - 1) AND p1.name = p2.name
ORDER BY p1.name, p1.year

which returns ALL consecutive records but I only want records which meet 3 consecutive criteria, ie not Lyla:

Name    Year
-------------
Carol   1999
Carol   2000
Carol   2001
Carol   2002
John    2001
John    2002
John    2003
Lyla    1996
Lyla    1997

Is it possible to build on my code, eg add an extra criterion, to refine the selection without implementing the row_number() method?

I would like the following output:

Name

Carol John

ie all records if there are at least 3 consecutive

Answer 1

Consider it as a gaps-and-islands problem and use the following trick to group consecutive rows together:

WITH cte1 AS (
    SELECT *, Year - ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Year) AS grp
    FROM t
), cte2 AS (
    SELECT *, COUNT(*) OVER (PARTITION BY Name, grp) AS grp_count
    FROM cte1
)
SELECT *
FROM cte2
WHERE grp_count >= 3
ORDER BY Name, Year

If you look at the values in grp column you will find the pattern.

db<>fiddle

Answer 2

I would simply use lead():

select distinct name
from (select p.*,
             lead(year, 2) over (partition by name order by year) as year_2
      from participation p
     ) p
where year_2 = year + 2;

For each row, this looks at the row two ahead for the same name ordered by year. If that row is the current year plus 2, then you have three years in a row.