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

select
	a.uid,
	days_window,
	round(b.`avg` * a.days_window, 2)avg_exam_cnt
from
	(
	select
		uid, max(days_window)days_window
	from
		(
		select
			uid, datediff((
			select
				start_time
			from
				exam_record
			where
				uid in (
				select
					uid
				from
					exam_record
				where
					year(start_time)= 2021
				group by
					uid
				having
					count(distinct date_format(start_time, '%Y-%m-%d'))>1)
				and r.uid = uid
				and r.start_time < start_time
			order by
				start_time
			limit 1),start_time)+ 1 days_window
		from
			exam_record r
		where
			uid in (
			select
				uid
			from
				exam_record
			where
				year(start_time)= 2021
			group by
				uid
			having
				count(distinct date_format(start_time, '%Y-%m-%d'))>1)
			and year(start_time)= 2021
		order by
			start_time)s
	group by
		uid)a
left join (
	select
		uid, count(date_format(start_time, '%Y-%m-%d'))/(datediff(max(start_time),min(start_time))+1) `avg`
	from
		exam_record
	where
		year(start_time)= 2021
	group by
		uid
	having
		count(distinct date_format(start_time, '%Y-%m-%d'))>1) b on
	a.uid = b.uid
order by
	days_window desc,
	avg_exam_cnt desc;