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

select uid,
(max(datediff(next_time,start_time)) + 1) as days_window,
round(count(uid) / (datediff(max(start_time),min(start_time)) + 1) * (max(datediff(next_time,start_time)) +1) ,2) as avg_exam_cnt
from
(select uid,start_time,
lead(start_time) over(partition by uid order by start_time) as next_time
from exam_record
where year(start_time) = 2021) tb1
group by uid
having count( distinct uid,date(start_time)) >=2
order by days_window desc,avg_exam_cnt desc