比较综合

1. 窗口函数：lead()over() first_value()over() last_value()over()
2. 时间做差函数:datediff(大,小)
3. 注意要的是2021年数据

with t as 
(select uid,
        date(start_time) as ftime,date(lead(start_time,1)over(partition by uid order by start_time)) as ltime,
        datediff(LAST_VALUE(DATE(start_time))over(partition by uid),FIRST_VALUE(DATE(start_time))over(partition by uid))+1 as t_gap
from exam_record
where year(start_time)=2021)
select *
from
(select uid,if(max(DATEDIFF(ltime,ftime))>1,max(DATEDIFF(ltime,ftime))+1,0) as days_window,
           round(count(1)/t_gap*if(max(DATEDIFF(ltime,ftime))>1,max(DATEDIFF(ltime,ftime))+1,0),2) as avg_exam_cnt
from t
group by uid) t1
where days_window <>0
order by days_window desc,avg_exam_cnt desc