题解 | #连续两次作答试卷的最大时间窗#比较臃肿，但能过

一开始使用day，但跨月的时候会出问题。 所以使用dayofyear。再锁定year在2021年。

select uid,days_window,avg_exam_cnt from (
select uid,days_window,dense_rank()over(partition by uid order by days_window desc) rk  ,round((cnt/gap)*days_window,2) avg_exam_cnt
 from (
select *,dayofyear(lag(start_time,1)over(partition by uid order by start_time desc))-dayofyear(start_time)+1 days_window,
max(dayofyear(start_time))over(partition by uid )-min(dayofyear(start_time))over(partition by uid)+1 gap,
count(uid)over(partition by uid) cnt from exam_record	
where start_time like '2021%')  a
where days_window > 1 and days_window is not null ) b
where rk =1
order by days_window desc,avg_exam_cnt desc