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

select uid,max(days_window) as days_window
,round(max(days_window)*max(sum_exam_cnt)/(1+timestampdiff(day,max(min_day),max(max_day))),2) as avg_exam_cnt
from 
(
    select uid
    ,max(days_window)over(partition by uid order by days_window desc) as days_window
    ,count(exam_id) over(partition by uid) as sum_exam_cnt
    ,(case when rk1=1 then y_m_d end ) as min_day
    ,(case when rk2=1 then y_m_d end ) as max_day
    from 
    (
        select uid,y_m_d,exam_id,rk1,rk2
        ,timestampdiff(day,lag(y_m_d) over(partition by uid order by rk1),y_m_d)+1 as days_window
        from 
        (
            select *
            ,date_format(start_time,"%Y%m%d") as y_m_d
            ,rank() over(partition by uid order by date_format(start_time,"%Y%m%d")) as rk1
            ,rank() over(partition by uid order by date_format(start_time,"%Y%m%d") desc) as rk2
            from exam_record
            where uid in 
            (
                select uid 
                from exam_record
                where year(start_time)=2021
                group by uid 
                having count(distinct date_format(start_time,"%Y%m%d"))>=2
             ) and year(start_time)=2021
            order by y_m_d
        ) t
     ) t 
) t 
group by uid
order by days_window desc,avg_exam_cnt desc