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

WITH user_dates AS (
    SELECT 
        uid, 
        DATE(start_time) AS exam_date
    FROM exam_record 
    WHERE YEAR(start_time) = 2021
),
filtered_users AS (
    SELECT uid 
    FROM user_dates 
    GROUP BY uid 
    HAVING COUNT(DISTINCT exam_date) >= 2 
),
sorted_dates AS (
    SELECT 
        uid,
        exam_date,
        LAG(exam_date) OVER (PARTITION BY uid ORDER BY exam_date) AS prev_date
    FROM user_dates 
    WHERE uid IN (SELECT uid FROM filtered_users)
),
max_diff_per_user AS (
    SELECT 
        uid,
        MAX(DATEDIFF(exam_date, prev_date)) AS max_diff
    FROM sorted_dates 
    WHERE prev_date IS NOT NULL 
    GROUP BY uid 
),
user_stats AS (
    SELECT 
        er.uid,
        COUNT(*) AS total_cnt,
        DATEDIFF(MAX(DATE(er.start_time)), MIN(DATE(er.start_time))) + 1 AS total_days 
    FROM exam_record er 
    WHERE YEAR(er.start_time) = 2021 
        AND er.uid IN (SELECT uid FROM filtered_users)
    GROUP BY er.uid 
)
SELECT 
    m.uid,
    (m.max_diff + 1) AS days_window,
    ROUND( (s.total_cnt / s.total_days) * (m.max_diff + 1), 2 ) AS avg_exam_cnt 
FROM max_diff_per_user m 
JOIN user_stats s ON m.uid = s.uid 
ORDER BY days_window DESC, avg_exam_cnt DESC;