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

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

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

首先是建一个表存每个用户的最大时间窗。这里用到的方法是用窗口函数lead得到每个start_time下一个时间,然后用datediff得到两个时间的天数差,这里记得要给结果+1.然后用max得到最大时间窗。

with temp as
(select dc.uid, max(dc.diff) as days_window from
(select uid,
start_time,
datediff(lead(start_time) over (partition by uid order by start_time), start_time)+1 as diff
from exam_record
where year(start_time)=2021) as dc
group by dc.uid
having count(distinct date_format(start_time, "%Y-%m-%d"))>=2),

然后再开一个新表temp2存每日平均。

temp2 as 
(select uid,
count(start_time) / (datediff(max(start_time), min(start_time))+1) as day_avg
from exam_record
where year(start_time)=2021
group by uid)

最后把两个表连接再简单计算一下就OK了。

select uid, days_window,
round(days_window * day_avg,2) as avg_exam_cnt
from temp left join temp2 using(uid)
order by days_window desc, avg_exam_cnt desc

以下是完整版

with temp as
(select dc.uid, max(dc.diff) as days_window from
(select uid,
start_time,
datediff(lead(start_time) over (partition by uid order by start_time), start_time)+1 as diff
from exam_record
where year(start_time)=2021) as dc
group by dc.uid
having count(distinct date_format(start_time, "%Y-%m-%d"))>=2),

temp2 as 
(select uid,
count(start_time) / (datediff(max(start_time), min(start_time))+1) as day_avg
from exam_record
where year(start_time)=2021
group by uid)

select uid, days_window,
round(days_window * day_avg,2) as avg_exam_cnt
from temp left join temp2 using(uid)
order by days_window desc, avg_exam_cnt desc
全部评论

相关推荐

05-29 22:11
门头沟学院 Java
Elastic90:抛开学历造假不谈,这公司的招聘需求也挺怪的,Java开发还要求你有图文识别、移动端开发和c++的经验,有点逆天了。
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
5
1
分享

创作者周榜

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