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

with t as
(select uid, exam_id, date_format(start_time, '%Y-%m-%d') day,
dense_rank() over (partition by uid order by date_format(start_time, '%Y-%m-%d')) r
from exam_record
where year(start_time) = 2021)

select a.uid, max(datediff(day2, day1) + 1) days_window, round(count(distinct exam_id, start_time) / (datediff(max(day2), min(day1)) + 1) * max(datediff(day2, day1) + 1), 2) avg_exam_cnt
from
(select t1.uid uid, t1.day day1, t2.day day2
from t t1
join t t2
on t1.uid = t2.uid and t1.r + 1 = t2.r) a
left join exam_record e
on a.uid = e.uid
where year(start_time) = 2021
group by a.uid order by days_window desc, avg_exam_cnt desc;

思路有点乱，尝试了十几次才成功。