题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
http://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
1.首先计算2021年至少有两天作答过试卷的人，确定好uid（下划线部分）
2.其次分别利用窗口函数计算days_window及avg_exam_cnt，
最后讲两张表合并即可
3.注意做了几张试卷并没有说要做完，因此计算avg_exam_cnt时需要用start_time进行计数
(select uid,max(w)+1 as days_window from
(select uid,datediff(submit_time,pre) as w
from
(select uid,submit_time,LAG(submit_time,1) over(partition by uid order by submit_time) as pre from
exam_record where year(submit_time)=2021 and uid in
(select uid from exam_record where year(submit_time)=2021
group by uid having count(distinct date_format(submit_time,'%m%d'))>=2)) a) b group by uid
) a
join
(select uid,count(start_time)/(datediff(max(start_time),min(start_time))+1) as cd
from exam_record where year(start_time)=2021 and uid in
(select uid from exam_record where year(submit_time)=2021
group by uid having count(distinct date_format(submit_time,'%m%d'))>=2) group by uid
) b
on a.uid=b.uid order by days_window desc,avg_exam_cnt desc
