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

with uid_days_windows as ( select uid, max(day_diff) days_window from ( select uid, start_time, lag(start_time,1)over(partition by uid order by start_time) last_start_time, timestampdiff(day, lag(date_format(start_time,'%Y%m%d'),1)over(partition by uid order by start_time), date_format(start_time,'%Y%m%d'))+1 day_diff from exam_record where year(start_time)=2021 and submit_time is not null )t where day_diff >= 2 group by uid ), uid_avg_exam as ( select uid, sum_anwser_num/sum_day_num all_avg_exam_cnt from ( select uid, count(exam_id) sum_anwser_num, timestampdiff(day, date_format(min(start_time),'%Y%m%d'), date_format(max(start_time),'%Y%m%d'))+1 sum_day_num from exam_record where year(start_time)=2021 group by uid order by start_time )t2 )

select uid, days_window, round(all_avg_exam_cnt*days_window,2) avg_exam_cnt from uid_days_windows udw left join uid_avg_exam uae using(uid) group by udw.uid order by days_window desc,avg_exam_cnt desc