【牛客】SQL138 连续两次作答试卷的最大时间窗-窗口函数

描述

���有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：

解释：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；

用户1005在20210905做了两张试卷，但是只有一天的作答记录，过滤掉。

with cte1 as (    select    uid,date_format(start_time,'%Y%m%d') as act_time,    dense_rank() over(partition by uid order by date_format(start_time,'%Y%m%d') desc) as rnk    from    exam_record    where uid in(        select uid from exam_record        where left(start_time,4)=2021        group by uid        having count(distinct left(start_time,10))>=2)    and left(start_time,4)=2021),cte2 as (    select    uid,count(act_time)/(datediff(max(act_time),min(act_time))+1) as rate    from cte1     group by uid),cte3 as (    select    a.uid as uid,    round(max(datediff(a.act_time,b.act_time)+1),2) as days_window    from    cte1 a left join cte1 b    on a.uid=b.uid and a.rnk+1=b.rnk    group by a.uid)selectuid,days_window,round(days_window*rate,2) as avg_exam_cntfrom cte2 left join cte3 using(uid)order by days_window desc,avg_exam_cnt desc

ps：在做连接查询的时候使用cte2 left join cte3没有问题，使用cte3 left join cte2会报错，有知道这是什么问题的友友可以在评论区留言，感谢