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

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

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

with uid_days_windows as ( select uid, max(day_diff) days_window from ( select uid, start_time, lag(start_time,1)over(partition by uid order by start_time) last_start_time, timestampdiff(day, lag(date_format(start_time,'%Y%m%d'),1)over(partition by uid order by start_time), date_format(start_time,'%Y%m%d'))+1 day_diff from exam_record where year(start_time)=2021 and submit_time is not null )t where day_diff >= 2 group by uid ), uid_avg_exam as ( select uid, sum_anwser_num/sum_day_num all_avg_exam_cnt from ( select uid, count(exam_id) sum_anwser_num, timestampdiff(day, date_format(min(start_time),'%Y%m%d'), date_format(max(start_time),'%Y%m%d'))+1 sum_day_num from exam_record where year(start_time)=2021 group by uid order by start_time )t2 )

select uid, days_window, round(all_avg_exam_cnt*days_window,2) avg_exam_cnt from uid_days_windows udw left join uid_avg_exam uae using(uid) group by udw.uid order by days_window desc,avg_exam_cnt desc

全部评论

相关推荐

门口唉提是地铁杀:之前b站被一个游戏demo深深的吸引了。看up主页发现是个初创公司,而且还在招人,也是一天60。二面的时候要我做一个登录验证和传输文件两个微服务,做完要我推到github仓库,还要我加上jaeger和一堆运维工具做性能测试并且面试的时候投屏演示。我傻乎乎的做完以后人家跟我说一句现在暂时不招人,1分钱没拿到全是白干
你的秋招第一场笔试是哪家
点赞 评论 收藏
分享
后来123321:别着急,我学院本大二,投了1100份,两个面试,其中一个还是我去线下招聘会投的简历,有时候这东西也得看运气
无实习如何秋招上岸
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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