SQL29 连续两次作答试卷的最大时间窗

题目主要信息：

• 计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序
• 历史规律是指在某用户有回答试卷的这些天，总共回答的试卷数 / 回答试卷的最后一天与第一天之间的天数
• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

问题拆分：

• 首先从筛选出2021年用户ID、开始作答试卷的时间、该次作答下一次的时间：
  • 用户ID、开始时间直接筛选，条件是时间为2021年。知识点：where、year()
  • 用函数获取这个用户该次作答后的下一次作答时间。知识点：lear()over() lear() over()函数用于某一个字段后N个序列，我们这里是获取以uid为分组的以start_time升序为排列的下1个（lead函数第2个参数为1）数据。lead(start_time, 1) over(partition by uid order by start_time asc) as next_time
• 从上述筛选出来的数据中用户ID、最大时间窗口、答试卷总次数、回答试卷的最后一天与第一天之间的天数：
  • 用户ID直接获取，因为这个是获取每个用户的上述信息，因此要以uid为分组。知识点：group by
  • 最大时间窗为上述信息中的本次作答时间与下一次作答时间之间的日期差值加1。max(datediff(next_time, start_time)) + 1 as days_window 知识点：max()、datediff()
  • 对每个分组的作答开始时间计数即是这个用户的总答题次数。count(start_time) as exam_count 知识点：count()
  • 取每个分组的最大日期与最小日期之间的日期差加1就是答题总天数。datediff(max(start_time), min(start_time)) + 1 as total_days
  • 最后要过滤掉刷题天数不足两天的用户，对每个分组计算不重复的日期数。having count(distinct date(start_time)) >= 2 知识点：having、count()、distinct
• 按最大时间窗和平均做答试卷套数倒序排序。知识点：order by

代码：

select uid, days_window, 
       round(days_window * exam_count / total_days, 2) as avg_exam_cnt
from(
    select uid,
           max(datediff(next_time, start_time)) + 1 as days_window,
           count(start_time) as exam_count,
           datediff(max(start_time), min(start_time)) + 1 as total_days
    from(
        select uid, start_time,
               lead(start_time, 1) over(partition by uid order by start_time asc) as next_time
        from exam_record
        where year(start_time) = 2021
    ) time_table
    group by uid
    having count(distinct date(start_time)) >= 2
) window_table
order by days_window desc, avg_exam_cnt desc