题解 | #连续两次作答试卷的最大时间窗#

连续两次作答试卷的最大时间窗

http://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c

1.首先计算2021年至少有两天作答过试卷的人,确定好uid(下划线部分)
2.其次分别利用窗口函数计算days_window及avg_exam_cnt,
最后讲两张表合并即可
3.注意做了几张试卷并没有说要做完,因此计算avg_exam_cnt时需要用start_time进行计数

select a.uid,days_window,round(days_window*cd,2) as avg_exam_cnt from
(select uid,max(w)+1 as days_window from
(select uid,datediff(submit_time,pre) as w
from
(select uid,submit_time,LAG(submit_time,1) over(partition by uid order by submit_time) as pre from
exam_record where year(submit_time)=2021 and uid in
(select uid from exam_record where year(submit_time)=2021
group by uid having count(distinct date_format(submit_time,'%m%d'))>=2)) a) b group by uid
) a
join
(select uid,count(start_time)/(datediff(max(start_time),min(start_time))+1) as cd
from exam_record where year(start_time)=2021 and uid in
(select uid from exam_record where year(submit_time)=2021
group by uid having count(distinct date_format(submit_time,'%m%d'))>=2) group by uid
) b
on a.uid=b.uid order by days_window desc,avg_exam_cnt desc
    

全部评论

相关推荐

不愿透露姓名的神秘牛友
今天 12:02
ssob上原来真有BOSS啊
硫蛋蛋:这种也是打工的,只不是是给写字楼房东打工
点赞 评论 收藏
分享
牛客83700679...:简历抄别人的,然后再投,有反馈就是简历不行,没反馈就是学历不行,多投多改只要技术不差机会总会有的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务