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

select uid,
# 取用户连续2次作答试卷的最大值
max(days_window) days_window,
# 用户累计作答的试卷数/用户作答试卷的最大时间窗=用户平均每天作答的试卷数
# 用户平均每天作答的试卷数*用户连续2次作答试卷的最大值=avg_exam_cnt
round(max(exam_cnt)/max(max_days_window)*max(days_window),2) avg_exam_cnt
from
(select uid,date(start_time) s_day,
# 通过偏移窗口函数计算连续2次作答试卷的时间窗
#第二次作答试卷的时间 lead(date(start_time))over(partition by uid order by date(start_time))
timestampdiff(day,date(start_time),lead(date(start_time))over(partition by uid order by date(start_time)))+1 days_window,
# 每个用户在统计时间内，第一次和最后一次作答试卷的时间窗
timestampdiff(day,min(date(start_time))over(partition by uid),max(date(start_time))over(partition by uid))+1 max_days_window,
# 每个用户累计作答的试卷数
count(exam_id)over(partition by uid) exam_cnt
from exam_record where year(start_time)=2021)t
where uid in 
( select uid from
#  通过排序可筛选出用户作答的天数，dense_rank()可对相的日期不增加排名，
#  并通过排名筛选出最少有2天作答的用户uid
    (select uid,
dense_rank()over(partition by uid order by date(start_time)) rk
from exam_record  where year(start_time)=2021)t1 where rk>=2)
group by uid
order by 2 desc,3 desc