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

#首先这个题就是**
#按条件计算公式
select
    tmp2.uid
    ,max(tmp2.diff_d) as days_window
    ,round(max(tmp2.cnt)/max(tmp2.max_d)*max(tmp2.diff_d),2) as avg_exam_cnt
from(#筛选满足条件的用户where
    select
         tmp1.uid
         ,tmp1.diff
         ,timestampdiff(day,substr(tmp1.first_d,1,10),substr(tmp1.last_d,1,10))+1 as max_d #计算出最大的时间差值
         ,timestampdiff(day,substr(tmp1.start_time,1,10),substr(tmp1.diff,1,10))+1 as diff_d #计算时间差
         ,tmp1.cnt #完成的试卷数
    from(#准备数据，窗口函数：max，min，count，lead
        select
            *
            ,max(start_time) over(partition by uid) as last_d #最近做题时间
            ,min(start_time) over(partition by uid) as first_d #最早做题时间
            ,count(exam_id) over(partition by uid) as cnt #总做题数目
            ,lead(start_time) over(partition by uid order by start_time) as diff #将后面的日期提前
        from exam_record
        where date_format(start_time,"%Y-%m-%d") BETWEEN '2021-01-01' and '2021-12-31'
        )tmp1
    where timestampdiff(day,tmp1.first_d,tmp1.last_d)>0
    )tmp2
group by tmp2.uid
order by days_window desc,avg_exam_cnt desc