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

with temp as (select *
                   , lead(start_time) over (partition by uid order by start_time) l
              from exam_record
              where year(start_time) = 2021)
select uid
     , max(timestampdiff(day, date_format(start_time, '%Y%m%d'), date_format(l, '%Y%m%d'))) + 1             days_window
     , round((count(1) /
              ((timestampdiff(day, min(date_format(start_time, '%Y%m%d')), max(date_format(start_time, '%Y%m%d')))) +
               1)) *
             (max(timestampdiff(day, date_format(start_time, '%Y%m%d'), date_format(l, '%Y%m%d'))) + 1), 2) avg_exam_cnt
from temp
group by uid
having count(distinct date_format(start_time, '%Y%m%d')) > 1
order by days_window desc, avg_exam_cnt desc;