题解 | #连续两次作答试卷的最大时间窗#

select uid,timediff,round(total*max(timediff)/max(diff_days),2) as  avg_exam_cnt from
(
    select uid,count(start_time) as total,max(DATEDIFF(recent_time, start_time))+1 as timediff,
    DATEDIFF(max(start_time), min(start_time))+1 as diff_days
from (select uid,start_time,lead(start_time)over(partition by uid order by start_time ) as recent_time
from exam_record where year(start_time) = 2021) as  newform 
group by uid 
having timediff >1
) as finalform 
group by uid
order by timediff desc,avg_exam_cnt desc

本题真的很难，原因是在于需要求出下一个开始日期，与之前的开始日期进行比较，求出相邻两天最大的时间间隔，然后利用max函数求出非相邻两天最大的时间间隔。还需要在子循环中记录总的考试数量，不然外部循环是获取不到的。