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

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

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

with cte_get_timewindow as (
    select uid,
    datediff(max(start_time),min(start_time))+1 as timediff,# 最长作答相隔天数
    max(datediff(nextday,start_time))+1 as days_window, # 连续作答的相隔天数,即'时间窗'
    count(start_time) as count_exam # 作答总数量
    from (
        select uid,exam_id,start_time,
        lead(start_time) over(partition by uid order by start_time) as nextday
		# 子查询中使用lead函数获取当前行下一行数据进行拼接,获取时间窗
        from exam_record
        where year(start_time) = 2021
    ) as t1
    group by uid
)
select uid,days_window,
round(count_exam/timediff*days_window,2) as avg_exam_cnt
# 根据最长作答时间差做卷数量规律计算在时间窗内做卷数量
from cte_get_timewindow
where timediff>1
order by days_window desc,avg_exam_cnt desc

全部评论

相关推荐

评论
点赞
收藏
分享

创作者周榜

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