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

with new as
(select
uid, exam_id, times, 
row_number() over (partition by uid order by times) rk
from
(select
uid, exam_id, date_format(start_time, '%Y-%m-%d') times
from 
exam_record
where year(start_time) = 2021
group by uid, exam_id, date_format(start_time, '%Y-%m-%d')
) t
)

select uid, days_window, avg_exam_cnt
from
(select
uid, max(df)+1 days_window,
round((max(df)+1) * (count(*) + 1) / (sum(df) +1), 2) avg_exam_cnt
from
(select
t1.uid, t1.exam_id, datediff(t2.times, t1.times) df
from 
new t1
inner join
new t2
on t1.rk = t2.rk-1 and t1.uid  = t2.uid
order by t1.uid
) t3
group by uid
) t4
where avg_exam_cnt <> 2.00
order by days_window desc, avg_exam_cnt desc