select id from (select *,sum(case when datediff(date-lag_date)<=1 and rate>=0.5 and lag_rate>=0.5 then 0 else 1                   end) over(partiton by id rows between unbounded preceding and current row) as s   from (select id,date,rate,            lag(date,1,null) over(partition by id order by date) as lag_date,            lag(rate,1,null) over(partition by id order by date) as lag_rate            from table)a )tab group by id,s having count(1)>=3