题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
select uid,days_window,round(exam_cnt/days_off*days_window,2) as avg_exam_cnt from( select uid,count(start_time) as exam_cnt, datediff(max(start_time),min(start_time)) + 1 as days_off, max(datediff(next_start_time,start_time)) + 1 as days_window from( select *,lead(start_time)over(partition by uid order by start_time) as next_start_time from exam_record where year(start_time) = 2021) as t1 group by uid) as t2 where days_off > 1 order by days_window desc,avg_exam_cnt desc
先利用lead函数找到下一次的答题时间,然后按照题目要求去找最大时间间隔和答题的时间差,最后按照题目要求输出即可。