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

最大时间窗为某一用户的所有做大时间最大值和最小值相差的天数，类似极值。

其历史规律就是该用户作答的平均值，未来的规律就是利用平均值乘以最大时间窗。

select uid,days_window,
round(days_window*exam_count/total_days,2) as avg_exam_cnt
from
(select uid,
max(datediff(next_time,start_time))+1 as days_window,
count(start_time) as exam_count,
datediff(max(start_time),min(start_time))+1 as total_days
from
(select uid,
 start_time,
 lead(start_time,1) over(partition by uid order by start_time asc) as next_time
from exam_record
where year(start_time)=2021)a
group by uid
having count(distinct date(start_time))>=2)b
order by days_window desc,avg_exam_cnt desc;