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

# 先找出2021年的数据，再找出当前行的上一行内容
WITH t1 AS (
    SELECT *,
           LAG(start_time) OVER (PARTITION BY uid ORDER BY start_time) AS lest_exam
    FROM exam_record WHERE year(start_time) = 2021)
	# t2是计算出两个时间的差值，并加一，因为需要将开始和结束时间的那天也算进去
	,t2 as(
         SELECT *, DATEDIFF(start_time, lest_exam) + 1 AS diff
         FROM t1
         )
# t3是分组聚合，求出每个id的考试数，连着两次考试日期差值的最大值，最大、最小考试时间的差值并加一，原因同t2里的
   , t3 AS (
         SELECT uid,
                COUNT(exam_id)                                 AS exam_num,
                MAX(diff)                                      AS diff,
                DATEDIFF(MAX(start_time), MIN(start_time)) + 1 AS days
         FROM t2
         GROUP BY uid)
# 筛选出，连着两次考试日期差值的最大值不是空的，以及最大、最小考试时间的差值并加一大于1的【就是有两天及以上参加考试的】，再计算相应的值
SELECT uid,diff as days_window, round(exam_num/days*diff,2) as avg_exam_cnt FROM t3 WHERE diff IS NOT NULL AND days > 1 ORDER BY days_window DESC ,avg_exam_cnt DESC ;