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

select
    uid
    ,days_window
    ,round(ct/all_day*days_window,2) avg_exam_cnt
from(
    select 
        uid
        ,max(ifnull(days_window,0))+1 days_window
        ,sum(ifnull(days_window,0)) sum_days
        ,avg(all_day)+1 all_day
        ,avg(ct) ct
    from(
        select 
            uid
            ,datediff(start_time,lead_time) days_window
            ,datediff(max_time,min_time) all_day 
            ,count(*) over(partition by uid) ct
        from(
            select 
                uid
                ,start_time
                ,lead(start_time,1) over(partition by uid order by start_time desc) lead_time 
                ,max(date(start_time)) over(partition by uid) max_time
                ,min(date(start_time)) over(partition by uid) min_time
            from exam_record
            where year(start_time) = 2021
        )a
    )b
    # where max(ifnull(days_window,0)) >=1 
    group by uid                                                        
)c
where days_window > 1
order by days_window desc,avg_exam_cnt desc