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

# 先找到2021年至少两天作答的用户
with t1 as (
    select uid, id, date_format(start_time, '%Y-%m-%d') as day
    from exam_record
    where 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
    )
)
select uid, max(daywindow) as days_window, round(max(daywindow) * (count(*) / (timestampdiff(day, min(day), max(day)) + 1)), 2) as avg_exam_cnt
from (
    select id, uid, day, timestampdiff(day,lag(day) over(partition by uid order by day), day) + 1 as daywindow
    from t1
    where year(day) = '2021'

) as a
group by uid
order by days_window desc, avg_exam_cnt desc;