原始数据

最终SQL

select id,date1,count(*) as day_cnt 
from (select id,date_add(date,-row_number() over(partition by id order by date)) as date1 
        from (select id,substr(date,1,10) as date
                from test
               group by id,substr(date,1,10)
             )a
     )b 
group by id,date1
having count(*) > 3