题解 | #连续两次作答试卷的最大时间窗#这个日期搞得头晕

select t1.uid,max(days_window)  as days_window,
       round(max(days_window) * avg_exam_cnt,2) as avg_exam_cnt
from (
        select uid,datediff(next_time,start_time) + 1 as days_window
        from (
                select uid,start_time,lead(start_time,1)over(partition by uid order by start_time) as next_time
                from (
                    select uid,start_time
                    from exam_record
                    where year(start_time) = 2021
                     ) t
            ) t3
    ) t1
join (select uid,round(count(1)/(datediff(max(start_time),min(start_time))+1) ,6) as avg_exam_cnt
            from exam_record
            where year(start_time) = 2021
            group by uid
     ) t2
on t1.uid = t2.uid
where t1.uid in(
            select uid
            from exam_record 
            where year(start_time) = 2021
            group by uid
            having count(distinct date_format(start_time,'%Y%m%d')) >= 2
            )
group by t1.uid
order by days_window desc,avg_exam_cnt desc