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

 -- 请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，
-- 那么根据该年的历史规律他在days_window天里平均会做多少套试卷，
-- 按最大时间窗和平均做答试卷套数倒序排序。
with
    user_with_multiple_days as (
        select
            uid,
            exam_id,
            date(start_time) as start_time
        from
            exam_record
        where
            uid in (
                select
                    uid
                from
                    exam_record
                where
                    year(start_time) = 2021
                group by
                    uid
                having
                    count(distinct date(start_time)) >= 2
            )
            and year(start_time) = 2021
    ),
    max_avg_temp as (
        select
            u1.uid,
            (
                select
                    count(start_time)
                from
                    user_with_multiple_days
                where
                    uid = u1.uid
            ) / max(datediff(u2.start_time, u1.start_time) + 1) as max_avg
        from
            user_with_multiple_days u1
            join user_with_multiple_days u2 on u1.uid = u2.uid
            and u1.start_time < u2.start_time
        group by
            u1.uid
    ),
    max_windows_temp as (
        select
            uid,
            max(days_window) as days_window
        from
            (
                select
                    uid,
                    start_time,
                    datediff(
                        start_time,
                        lag(start_time) over (
                            partition by
                                uid
                            order by
                                start_time asc
                        )
                    ) + 1 as days_window
                from
                    user_with_multiple_days
            ) as t1
        group by
            uid
    )
select
    uid,
    days_window,
    round(days_window * max_avg, 2) as avg_exam_cnt
from
    max_avg_temp
    join max_windows_temp using (uid)
order by
    days_window desc,
    avg_exam_cnt desc