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

# 2021年 至少有两天作答过  连续两次做大试卷的最大时间窗days_window
# 根据历史期，在days_window中平均做几套
# 解题重点在于LEAD的用法，当需要比较两个先后经历的时间时，考虑LEAD、LAG

SELECT 
	uid,
    MAX(DATEDIFF(next_start_time, start_time) + 1) AS days_window,
	ROUND(COUNT(*)/(DATEDIFF(MAX(start_time), MIN(start_time)) + 1) * MAX(DATEDIFF(next_start_time, start_time) + 1), 2) AS avg_exam_cnt
FROM(
	SELECT 
		uid, start_time,
		LEAD(start_time, 1, NULL) OVER(
										PARTITION BY uid
										ORDER BY start_time
										) AS next_start_time
	FROM exam_record                                    
	WHERE YEAR(start_time) = 2021
	) AS new_table1
GROUP BY uid
HAVING days_window > 1
ORDER BY days_window DESC, avg_exam_cnt DESC