题解 | #计算商场折扣#

1. 筛选出2021年有作答过试卷的用户并用LEAD()OVER()函数计算下一次作答时间，创建新表
2. 计算最大时间窗days_window，用函数MAX()和DATEDIFF()（发现需要知道下一次作答时间，故在步骤1筛选出计算下一次作答时间）,计算作答试卷总间隔天数和作答的试卷总数（在新表中筛选）

SELECT uid, days_window,
    ROUND(exam_cnt_2021 / total_days * days_window, 2) AS avg_exam_cnt
FROM (
    SELECT uid,
        COUNT(start_time) AS exam_cnt_2021,
        MAX(DATEDIFF(next_start_time, start_time)) + 1 AS days_window,
        SUM(DATEDIFF(next_start_time, start_time)) + 1 AS total_days
    FROM (
        -- 筛选2021年用户作答表和计算用户下一次作答时间
        SELECT uid, start_time,
            LEAD(start_time, 1, 0) OVER (PARTITION BY uid ORDER BY start_time) AS next_start_time
        FROM exam_record 
        WHERE YEAR(start_time) = '2021'
    ) table_2021
    GROUP BY uid
    HAVING total_days > 1
)calcu_table
ORDER BY days_window DESC, avg_exam_cnt DESC