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

with zb as
(with e3 as 
(select uid,start_time,px,s_sum,datediff(t_a,t_i) + 1 as max_day
from 
(select *,count(uid) over(partition by uid) s_sum,max(start_time) over(partition by uid) t_a,
min(start_time) over(partition by uid) t_i
from
(select uid,start_time,row_number() over(partition by uid order by start_time) as px
from exam_record
where year(start_time) = 2021) e1) e2)
select ee3.uid,max(datediff(ee4.start_time,ee3.start_time) + 1) as days_window,
max(ee3.s_sum) s_sum,max(ee3.max_day) max_day
from e3 as ee3,e3 ee4
where ee3.uid = ee4.uid and ee3.px + 1 = ee4.px
group by ee3.uid
having days_window >1)
select uid,days_window,round((s_sum/max_day)*days_window,2) as avg_exam_cnt
from zb
group by uid
order by days_window desc,avg_exam_cnt desc;