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

SELECT uid, 
        MAX(day_window) AS days_window,
        ROUND(MAX(times)/MAX(day_total)*MAX(day_window), 2) AS avg_exam_cnt
FROM (
        SELECT uid,
            start_time,
        DATEDIFF(start_time, lag(start_time,1) over (partition by uid order by start_time))+1 
        AS day_window,
        COUNT(exam_id) OVER (PARTITION BY uid) AS times,
        DATEDIFF(FIRST_VALUE(start_time) over (partition by uid order by start_time DESC),
        FIRST_VALUE(start_time) over (partition by uid order by start_time))+1 AS day_total
    FROM exam_record
    WHERE uid IN (
        SELECT uid
        FROM exam_record
        WHERE YEAR(start_time) = "2021"
        GROUP BY uid
        HAVING COUNT(exam_id) >= 2
    ) AND YEAR(start_time) = "2021"
) data
GROUP BY uid
HAVING COUNT(DISTINCT DATE(start_time) ) >1
ORDER BY days_window DESC, avg_exam_cnt DESC