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

select uid,max(间隔) as days_window,
round(sum(试卷数量)/(datediff(max(做答日期),min(做答日期))+1)*max(间隔),2) as avg_exam_cnt
#	3								/7				*6
from(
	select *,datediff(lead(做答日期,1) over(partition by uid order by 做答日期),做答日期)+1 间隔
	from(
		select uid,date(start_time) 做答日期,count(exam_id) 试卷数量
		from exam_record
		where year(start_time) =2021	#2021年用户做答日期
		group by uid,date(start_time)
		) as 计算间隔
	) as 计算最大时间窗
group by uid 
having days_window >0  #至少做答两天
order by days_window desc,avg_exam_cnt desc;