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

select
    uid,
    max(datediff(start_time,lastday)) + 1 as days_window,
    round(count(*) /(datediff(max(start_time),min(start_time)) + 1) * (max(datediff(start_time,lastday)) + 1),2) as avg_exam_cnt
from (
    select 
        *,
        lag(start_time,1) over(
            partition by uid 
            order by start_time) as lastday
    from exam_record
    where year(start_time) = 2021 
) as t
group by uid
having count(distinct date(start_time)) >= 2
order by days_window desc, avg_exam_cnt desc

同一天作答两次要留意一下