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

题目：

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。

总体思路：

首先列出本次作答时间（start_time）和上一次作答时间 lead1 然后求解最大时间窗 days_window 以及avg_exam_cnt （这里可以使用两层查询逐步求，也可以一层查询直接求）

易错

• 时间差：datediff(大时间，小时间)和timestampdiff(精确单位，小时间，大时间)不同
• 时间窗：＋1，1号和6号都作答过，那么时间窗就是6-1+1=6天。
• 外层查询where “至少有两天作答过试卷” 表示方法有两种：
  1. having datediff(max(start_time),min(start_time))+1>1 # 两天之差>1
  2. having count(distinct date(start_time)) > 1 # 提交日期不止一天

两层查询

select uid,
       max(datediff(lead1,start_time))+1 as days_window,   
       round((max(datediff(lead1,start_time))+1)*count(start_time)/(datediff(max(start_time),min(start_time))+1),2) as avg_exam_cnt
from (
     select uid,
             start_time,
             lead(start_time,1) over(partition by uid
                                     order by start_time) as lead1
     from exam_record
     where year(start_time)=2021
    ) a  -- 显示两次作答时间
group by uid
having datediff(max(start_time),min(start_time))>0
-- having count(distinct date(start_time)) > 1
order by days_window desc,avg_exam_cnt desc

三层查询

select uid,
    days_window,
    round(days_window*cnt/days_diff,2) avg_exam_cnt
from (
    select uid,
            max(datediff(lead1,a.start_time)+1) as days_window,
            count(start_time) as cnt,
            datediff(max(a.start_time),min(a.start_time))+1 days_diff
      from 
            (select uid,
             exam_id,
             start_time,
             lead(start_time,1) over(partition by uid
                                     order by start_time) as lead1
            from exam_record
            where year(start_time)=2021
            ) a  -- 显示两次作答时间
    group by uid) b
where days_diff>1
order by days_window desc,avg_exam_cnt desc