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

#需求：计算在2021年至少有两天作答过试卷的人中，连续两次作答试卷的最大时间窗days_window，根据该年的历史规律他在days_window天里平均会做多少套试卷
#输出：uid、days_window(最长的一次时间间隔)、avg_exam_cnt(平均每天答卷数)
#要求：人员选区为2021年中至少有两天作答过的人；days_window要2021年中连续两次作答间隔最大的一次；avg_exam_cnt为在该时间窗中日均答卷数；输出按days_window和avg_exam_cnt倒序，avg_exam_cnt保留两位小数
#材料解释部分：用户1006在2021年共作答3次；第一次作答到最后一次作答，全程7天；最长的连续作答时间间隔为6天；那么它全程中，日均作答3/7=0.428张试卷，在最长的days_window中，日均作答0.428*6=2.57张试卷。即avg_exam_cnt求解公式为（用户作答次数/用户第一次作答到最后一次作答的日期差）*days_window
with w1 as(
    select uid,start_time,
    #第一次作答时间，sql默认asc排序，而且需求不涉及完成时间，这里可以直接取
    lead(start_time,1)over(partition by uid order by start_time) ck1
    #第二次作答时间，这里是开窗向下偏移一行
    from exam_record
    where year(start_time)='2021'
)
#这里的第一、二次时间，其实就是同一用户的前后两次作答时间；这里的编写会输出所有的用户的所有连续作答时间
select uid,days_window,round((e1/e2)*days_window,2) avg_exam_cnt
from(
    select uid,max(datediff(ck1,start_time))+1 days_window,
    #得到最大的连续作答时间之间的间隔日期；+1的原因：例如datediff计算1-6号的时间差，得到的为5天，而依据材料解释部分，要求得到的是6天
    count(start_time) e1,datediff(max(start_time),min(start_time))+1 e2
    from w1
    group by 1
    having e2>=2
    #要求中至少有两天作答过的人，即第一次作答到最后一次作答全程必须大于1天
) w2
order by 2 desc,3 desc