create table bms
(seat_no int,is_empty varchar(10));
insert into bms values
(1,'N')
,(2,'Y')
,(3,'N')
,(4,'Y')
,(5,'Y')
,(6,'Y')
,
(7,'N')
,
(8,'Y'),
(9,'Y')
,(10,'Y') ,(11,'Y')
,(12,'N')
,(13,'Y');
with cte as (select seat_no, is_empty,
(seat_no-row_number ()
over (partition by is_empty order by seat_no)
) ct from bms
where is_empty ='y')
, cte2 as(select seat_no, is_empty,
count(ct) over(partition by ct,is_empty) k from cte)
select * from cte2
where k>2
order by seat_no;
with cte as (select seat_no, is_empty,
(seat_no-row_number ()
over (partition by is_empty order by seat_no)
) ct from bms
where is_empty ='y')
select * from cte/*
where ct in (select ct from cte group by ct having count(*)>2))
Telegram: DB developers channel