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

WITH UserActivity AS (
    SELECT 
        uid
    FROM 
        exam_record
    WHERE 
        YEAR(start_time) = 2021
    GROUP BY 
        uid
    HAVING 
        COUNT(DISTINCT DATE(start_time)) > 1
),
TimeWindows AS (
    SELECT 
        uid,
        start_time,
        DATEDIFF(
            LEAD(start_time) OVER (PARTITION BY uid ORDER BY start_time), 
            start_time
        ) + 1 AS sdays_window
    FROM 
        exam_record
    WHERE 
        YEAR(start_time) = 2021
),
TotalAttempts AS (
    SELECT
        uid,
        COUNT(*) AS total_attempts
    FROM
        exam_record
    WHERE
        YEAR(start_time) = 2021
    GROUP BY
        uid
)

SELECT
    tw.uid,
    MAX(tw.sdays_window) AS days_window,
    round(ta.total_attempts/(datediff(max(start_time),min(start_time))+1)*MAX(tw.sdays_window),2) AS avg_exam_cnt
FROM
    TimeWindows tw
JOIN
    UserActivity ua ON tw.uid = ua.uid
JOIN
    TotalAttempts ta ON tw.uid = ta.uid
GROUP BY
    tw.uid
ORDER BY
    days_window DESC,
    avg_exam_cnt DESC;
	
	不够简洁不过很通俗易懂版