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

select uid, days_window, round((cnt/totaltime*days_window),2) as avg_exam_cnt 
from
(select uid, 
max(datediff(start_time, before1)) +1 as days_window, 
#计算相邻时间最大间隔 max(start_time-before1) 要加1天
count(start_time) as cnt, 
datediff(max(start_time),min(start_time))+1 as totaltime 
#计算最大时间间隔 max-min 要加1天
from
(select uid, start_time,
lag(start_time,1) over(partition by uid order by start_time) as before1 #上个时间
from exam_record
where year(start_time) =2021) as a #筛选年份为2021的数据
group by uid) as b  #这里用了groupby 算完了所有的聚合(days_window,cnt, totaltime)
where totaltime >1   #最外层就不用groupby了
order by days_window desc, avg_exam_cnt desc

最坑的地方： 作答了就算1次，不用管交没交卷

所有的submit_time换成start_time 就成功了