题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
http://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
select
uid,
max(datediff(next_time,start_time))+1 as days_window,
round(count(start_time)/(datediff(max(start_time),min(start_time))+1)*(max(datediff(next_time,start_time))+1),2)as avg_exam_cnt
from(
select
uid,
start_time,
lead(start_time,1) over(partition by uid order by start_time) as next_time
from exam_record
where year(start_time) = '2021'
)a
group by uid
having count(distinct date(start_time)) > 1
uid,
max(datediff(next_time,start_time))+1 as days_window,
round(count(start_time)/(datediff(max(start_time),min(start_time))+1)*(max(datediff(next_time,start_time))+1),2)as avg_exam_cnt
from(
select
uid,
start_time,
lead(start_time,1) over(partition by uid order by start_time) as next_time
from exam_record
where year(start_time) = '2021'
)a
group by uid
having count(distinct date(start_time)) > 1
order by days_window desc,avg_exam_cnt desc
这里要记住MySQL的datediff和sql的不同。
