SQL29 连续两次作答试卷的最大时间窗
1、函数
lead (str1,number) over(partition by str2 order by str3)
题目
现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
请计算在2021年至少有两天作答过试卷的人中,计算该年连续两次作答试卷的最大时间窗days_window,那么根据该年的历史规律他在days_window天里平均会做多少套试卷,按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下:
select uid
,days_window
,round(days_window*exam_count/total_days,2) as avg_exam_cnt
from
(select
uid
,max(datediff(next_time,start_time))+1 days_window
,count(start_time) as exam_count
,datediff(max(start_time),min(start_time))+1 total_days
from
(select uid
,start_time
,lead(start_time,1) over(partition by uid order by start_time asc) as next_time
from exam_record
where year(start_time)=2021)a
group by uid
having datediff(max(start_time),min(start_time))+1>1)b
order by days_window desc,avg_exam_cnt desc