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

with table1 as (
    select uid, date_format(start_time,"%Y%m%d") as time 
    from exam_record
    where year(start_time) = 2021
),
table2 as(
    select * ,
    # 窗口函数数据迁移
    lead(time) over(partition by uid order by time) as next_time
    from table1
),
table3 as(
    select * ,(datediff(next_time,time) + 1) as interval_day,
    # 获取每个uid 的最近作答天数  和 最早作答天数
    max(time) over(partition by uid) as max_day,
    min(time) over(partition by uid) as min_day,
    # 计算时间间隔
    (datediff(max(time) over(partition by uid),min(time) over(partition by uid)) + 1) as daydiff
    from table2
)

select uid, max(interval_day) as days_window,
round((count(time) / max(daydiff)) * max(interval_day),2) as avg_exam_cnt
from table3
group by uid
having days_window != 1
order by days_window desc,avg_exam_cnt desc