日活中途间断一天（即员工上班第二天如果补卡也算连续）求出对应的人

select
name,fina_date,count(1) cnt 
from (select
 name,dk_date,date_add(dk_date,INTERVAL - rn day) fina_date
from 
(select
 name,dk_date,ROW_NUMBER() over(partition by name order by dk_date) rn
from
  (select
    name,
    case when rn=2 then DATE_ADD(date,INTERVAL -1 DAY) 
    else date end dk_date 
  from
    (
     select
       name,date,new_date,ROW_NUMBER() over(partition by name,date order by new_date ) rn
      
      from (
           select
             name ,date,DATE_ADD(date,INTERVAL -ra day) new_date
           from
            (select
             name,date,dense_rank() over(partition by name  order by date asc) ra
            from employee
            ) t
        )t1
      ) t2
     ) t3
    )t4
   )t5 group by name,fina_date having cnt>=7