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

#1.创建临时表t计算各记录对应的days_window和avg_exam_cnt，并筛选掉记录少于2和用户与考试不在2021年的记录
with t as(
        select
            uid,
            datediff(lead(start_time,1) over(partition by uid
                                             order by start_time asc),start_time)+1 as days_window,
            round(count(start_time) over(partition by uid)/(datediff(max(start_time) over(partition by uid), min(start_time) over(partition by uid))+1)*(datediff(lead(start_time,1) over(partition by uid       
                                             order by start_time asc),start_time)+1),2) as avg_exam_cnt
        from
            exam_record
        where
            uid in (
                select
                uid
                from
                exam_record
                group by 
                uid
                having
                count(distinct(date_format(start_time,'%Y%m%d')))>=2) and 
                start_time like '2021%'
),
#创建临时表t，以用户为固定窗口，对‘连续两次作答试卷的时间窗’排序，为后面选出每个人的最大时间窗做准备
b as (
    select
        uid,days_window,avg_exam_cnt,
       rank() over (partition by uid
                    order by days_window desc) as ranking
    from 
        t
    where
        avg_exam_cnt is not null)

#找出每个人ranking=1所对应的记录,得到最终结果，排序
select
    uid,days_window,avg_exam_cnt
from
    b
where
    ranking =1
order by
    days_window desc,avg_exam_cnt desc