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

select uid,max(time_diff)+1 days_window, round(count(start_time)/(sum(time_diff)+1)*(max(time_diff)+1),2) avg_exam_cnt from( select uid,start_time, timestampdiff(day,DATE_FORMAT(pre_time,'%Y-%m-%d'),DATE_FORMAT(start_time,'%Y-%m-%d')) as time_diff from( select uid,start_time, lag(start_time,1) over(partition by uid order by start_time) as pre_time from exam_record where year(start_time) = 2021 ) A )B group by uid having max(time_diff) > 0 order by days_window desc,avg_exam_cnt desc