题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
连续两次作答试卷的最大时间窗
http://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
比较综合
- 窗口函数：lead()over() first_value()over() last_value()over()
- 时间做差函数:datediff(大,小)
- 注意要的是2021年数据
with t as
(select uid,
date(start_time) as ftime,date(lead(start_time,1)over(partition by uid order by start_time)) as ltime,
datediff(LAST_VALUE(DATE(start_time))over(partition by uid),FIRST_VALUE(DATE(start_time))over(partition by uid))+1 as t_gap
from exam_record
where year(start_time)=2021)
select *
from
(select uid,if(max(DATEDIFF(ltime,ftime))>1,max(DATEDIFF(ltime,ftime))+1,0) as days_window,
round(count(1)/t_gap*if(max(DATEDIFF(ltime,ftime))>1,max(DATEDIFF(ltime,ftime))+1,0),2) as avg_exam_cnt
from t
group by uid) t1
where days_window <>0
order by days_window desc,avg_exam_cnt desc