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

select
t.uid,
max(datediff(t.m,t.pday))+1 as cnt,
round(
(t.puid/max((datediff(t.xday,t.mday))+1))*(max(datediff(t.m,t.pday))+1),2) as pcn
from(
select 
uid,
date(start_time) as pday,
lead(date(start_time))over(partition by uid order by date(start_time) asc) as m,
max(date(start_time))over(partition by uid) as xday,
min(date(start_time))over(partition by uid) as mday,
count(uid)over(partition by uid) as puid
from exam_record
where
year(start_time)=2021
) as t
where
datediff(t.m,t.pday)>1  
group by t.uid
order by cnt desc,pcn desc
明白题意就基本会了，这个题的要求的是:求每个人2次开始时间间隔最大的天数，（也就是说我9.1开始一次，我9.5开始一次，就算这2次时间差，找表里最大的一组。）然后在找到，2021年每个人最早的开始时间和最晚的开始差，及人次算均值，得到这个值在和间隔最大的天数相乘即答案。