题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
# (select uid,start_time,
# lead(start_time,1)over(partition by uid order by start_time) Nd
# from exam_record
# where year(start_time)=2021
# ) temp1
# 过滤2021年，并偏移时间1次
# (select uid,count(start_time) as Ts,
# max(datediff(Nd,start_time))+1 as days_window,
# datediff(max(start_time),min(start_time))+1 as TDs
# from temp1
# group by uid
# )temp2
# 找到总做题量Ts,连续2天最大窗口天数dw，和做题总天数TDs
# 最后把找到的这些套起来，过滤至少2天，倒序排列
select uid,days_window,round(Ts * days_window / TDs,2) avg_exam_cnt
from
(select uid,count(start_time) as Ts,
max(datediff(Nd,start_time))+1 as days_window,
datediff(max(start_time),min(start_time))+1 as TDs
from
(select uid,start_time,
lead(start_time,1)over(partition by uid order by start_time) Nd
from exam_record
where year(start_time)=2021
) temp1
group by uid
)temp2
where TDs>1
order by days_window desc,avg_exam_cnt desc
查看7道真题和解析