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

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

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
    

全部评论

相关推荐

每晚夜里独自颤抖:你cet6就cet6,cet4就cet4,你写个cet证书等是什么意思。专业技能快赶上项目行数,你做的这2个项目哪里能提现你有这么多技能呢
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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