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

with t1 as(
select
    uid,
    timestampdiff(day,date_format(lag(start_time) over(partition by uid order by start_time),'%Y%m%d'),date_format (start_time, '%Y%m%d') + 1) as days_window
from
    exam_record
where
    year(start_time) = 2021),
t2 as (
select
    uid,
    count(exam_id)/timestampdiff(day,date_format(min(start_time),'%Y%m%d'),date_format(max(start_time),'%Y%m%d')+ 1) as avg_exam
from
    exam_record
where
    year(start_time) = 2021 
group by
    uid)

select
    t1.uid,
    max(t1.days_window) as days_window,
    round(max(t1.days_window) * t2.avg_exam,2) as avg_exam_cnt
from
    t1
left join
    t2
on
    t1.uid = t2.uid
where
    days_window > 1
group by
    t1.uid
order by
    days_window desc,
    avg_exam_cnt desc