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

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