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

#按最大时间窗和平均做答试卷套数倒序排序，保留两位小数
WITH t AS (
	SELECT uid,DATE(start_time) start_time,
	row_number() over(PARTITION BY uid ORDER BY DATE(start_time)) rn,
	rank() over(PARTITION BY uid ORDER BY DATE(start_time)) rk
	FROM exam_record
	WHERE YEAR (start_time)='2021'
 ),
t1 AS (
	SELECT * FROM t WHERE uid IN (
		SELECT uid FROM t WHERE rk>=2
	)
),t2 AS (
SELECT *,
	lead(start_time) over(PARTITION BY uid ORDER BY start_time) tmp
FROM t1)
SELECT  uid,MAX(DATEDIFF(tmp,start_time))+1 AS days_window,
	ROUND(MAX(rn)/(DATEDIFF(MAX(start_time),MIN(start_time))+1)*(MAX(DATEDIFF(tmp,start_time))+1),2) AS avg_exam_cnt
FROM t2 GROUP BY uid
ORDER BY days_window DESC,avg_exam_cnt DESC;
题目太难了，估计得做一个小时；
这里有个易错点，一天中做了几套试卷都要计算进去，不要被题目最后一句话误导了；
还有个问题，不能拿submit_time作为基准计算，因为有些没有提交记录，也算作答有效。