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

select b.uid as uid, b.diff as days_window, round(b.diff*b.avg_cnt,2) as avg_exam_cnt
from
(
    select a.uid as uid, 
    max(datediff(a.start_time,a.new_time)+1) as diff, 
    count(*)/(1+datediff(max(a.start_time),min(a.start_time))) as avg_cnt
    from 
        ( 
            select uid, exam_id,start_time,submit_time,score,
            lag(start_time,1)over(partition by uid order by start_time) as new_time
            from exam_record
            where year(start_time)=2021
        )as a
    group by uid
    having count(distinct(date_format(start_time,'%Y%m%d'))) >=2) as b
order by days_window desc, avg_exam_cnt desc