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

/*计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。*/

with t1 as(
    SELECT uid,
    start_time,
    lead(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
    FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
),
t2 as (
    SELECT 
    uid,
    COUNT(start_time) total, -- 用户2021年作答的次数
    DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
    MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
    from t1
    group by uid
)
SELECT uid,days_window,ROUND(total* days_window/diff_time,2) avg_exam_cnt
FROM t2
WHERE diff_time>1
ORDER BY days_window DESC,avg_exam_cnt DESC

这里需要连续两次的作答时间，这时候就要想到偏移函数lead()，配合开窗函数，得到下一次作答时间列：

lead(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间

剩下的就酷驰酷驰写就完了