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

select
    uid,
    max(timestampdiff(day, last_date, cur_date) + 1) as days_window,
    round(
        count(exam_id) / (
            timestampdiff(day, min(cur_date), max(cur_date)) + 1
        ) * max(timestampdiff(day, last_date, cur_date) + 1),
        2
    ) as avg_count
from
    (
        select
            uid,
            exam_id,
            date(start_time) as cur_date,
            lag(date(start_time), 1) over (
                partition by
                    uid
                order by
                    start_time
            ) as last_date
        from
            exam_record
        where
            year(start_time) = 2021
            and uid in (
                select
                    uid
                from
                    exam_record
                where
                    year(start_time) = 2021
                group by
                    uid
                having
                    count(distinct date_format(start_time, 'Y%m%d%')) >= 2
            )
    ) as t
group by
    uid
order by
    days_window DESC,
    avg_count DESC