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

select
    uid,
    max(DATEDIFF(next_time,start_time))+1 as days_window, #最大时间窗days_window
    round(count(start_time)/(DATEDIFF(max(start_time),min(start_time))+1)#连续两次作答最大时间窗
    *(max(DATEDIFF(next_time,start_time)) +1)##最大时间窗days_window
    ,2) as avg_exam_cnt
from (
    select
        uid,
        start_time,
        lead(start_time,1) over(partition by uid order by start_time) as next_time
    from exam_record
    where year(start_time) = '2021'
)t1
group by uid
having count(distinct date(start_time)) > 1
order by days_window desc,avg_exam_cnt desc