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

# 计算该年连续两次作答试卷的最大时间窗days_window
# 字段：uid / 最大时间窗 / 最大时间窗内平均做卷数

# 表一：筛选最大时间以及最小时间(时间差) & 做的卷子数量
    with table1 as (
    select uid,min(date_format(start_time,'%Y%m%d')) as time1,
    max(date_format(start_time,'%Y%m%d')) as time2,
    count(exam_id) as exam_cnt,
    count(exam_id) / (timestampdiff(day,min(date_format(start_time,'%Y%m%d')),max(date_format(start_time,'%Y%m%d')))+1) as avg_cnt
    from exam_record
    where year(start_time) = 2021
    group by uid
    ),

# 可能需要用到 lead： 主要是使用lead 创造一个 next_time,后面输出每个uid点最大时间差days_windows
table2 as (
    select uid, date_format(start_time,"%Y%m%d") as time1,
    lead(date_format(start_time,"%Y%m%d"))over(partition by uid order by start_time) as next_time
    from exam_record
    where year(start_time) = 2021
)

# 表：构造连续日期时间差，筛选每个uid分类下的最大时间差作为days_windows
select uid,
max(timestampdiff(day,t2.time1,next_time))+1 as days_windows,
round((max(timestampdiff(day,t2.time1,next_time))+1) * avg_cnt,2) as avg_exam_cnt
from table2 t2 left join table1 t1 using(uid)
group by uid
having max(timestampdiff(day,t2.time1,next_time))+1 != 1
order by days_windows desc,avg_exam_cnt desc

# 注意点 是作答过。。