with cte as( select *, lag(item,1) over(order by id ) laag, lead(item,1) over(order by id) leaad from items_table),cte2 as ( select *,case when item=laag then 1 when item= leaad then 1 else 0 end as flag from cte),cte3 as ( select distinct(item),flag,count(flag) over(partition by item) as cnt from cte2 where flag=1) select item from cte3 where cnt>=3
select distinct item from (select * , dense_rank() over (partition by item order by id ) as dn from items_table) x where dn >=3 i am getting the same output, is this correct method? please let me know