with t as (

SELECT  

uid,date(start_time) as start_day,exam_id,

row_number() over(partition by uid order by start_time) as ranking

from exam_record )



SELECT t.uid,max(datediff(t1.start_day,t.start_day)+1) as days_window, count( t.exam_id),

round((count( t.exam_id)/(datediff(max(t.start_day),min(t.start_day))+1))*max(datediff(t1.start_day,t.start_day)+1),2) as avg_exam_cnt



 from t left join t as t1 on t.uid = t1.uid and t.ranking = t1.ranking-1

 where year(t.start_day)=2021

group by t.uid

having count(distinct date(t.start_day))>=2

order by days_window desc ,avg_exam_cnt desc

这个题的难点有两个
1、找到自联结求窗口值的思路，通过建立排名列作为辅助列来实现
2、平均作答试卷套数的计算
1. 做过的套数 不需要按试卷去重
2. 时间差需要+1天

[[窗口函数]]

使用lead