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

#1)筛选出至少2021年中至少有两天作答过试卷的人
#计算出至少两天打过试卷的uid
SELECT uid
    FROM exam_record
    GROUP BY uid
    HAVING COUNT(DISTINCT DATE(start_time))>=2
#2)算该年连续两次作答试卷的最大时间窗days_window
    #1. 开窗，根据uid分区，start_time排序[正序),下一次做的时间lead另起一行lead(start_time,1,null)，
    
    #下一次做卷子的时间；LEAD(start_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time)
    
    #2021最后一次做的时间 max：目的是为了计算2021年以来第一次做卷子和最后一次做试卷的时间差
    #两种方法：
    #第一种方法：datediff(max(date(start_time)), min(date(start_time)))
  
    #第二种方法：
    #(1)先开窗计算出最大时间[注意：这里有个坑，不能order by，不然最大就是自己
    #MAX(start_time) OVER(PARTITION BY uid)  as lastest_time
    #(2)再计算出最大的时间差
    #MAX(DATEDIFF(lastest_time,start_time) + 1)
   
    #2. 计算连续两次作答试卷时间窗，以及2021年来第一次和最后一次的相隔时间，这段时间做的试卷总数count(*)
    #连续两次作答试卷的时间窗
    #(1) 先开窗算出下一次的做题时间
    #LEAD(start_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time) AS next_time
    #(2) 再用datediff函数计算两次相隔时间
    #DATEDIFF(next_time,start_time) + 1
    
#3)用户在2021年days_window天里平均会做多少套试卷
    #最后就是按照uid分组取最大时间窗和这些天平均做多少套试卷
    #avg_exam_cnt = 总作答次数/最大时间差*最大时间窗
    #总作答次数：count(*)
    #最大时间窗：max(DATEDIFF(next_time,start_time) + 1)
    #最大时间差：MAX(DATEDIFF(lastest_time,start_time) + 1)
SELECT  
  uid,
  DATEDIFF(MAX(start_time), MIN(start_time)),
  MAX(DATEDIFF(next_time,start_time)) + 1 AS days_window,
  ROUND(COUNT(*)/MAX(DATEDIFF(lastest_time,start_time) + 1)* MAX(DATEDIFF(next_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_time,
  MAX(start_time) OVER(PARTITION BY uid) AS lastest_time
FROM 
  exam_record
WHERE uid IN 
(SELECT uid
    FROM exam_record
    GROUP BY uid
    HAVING COUNT(DISTINCT DATE(start_time))>=2)
AND 
YEAR(start_time) = 2021) t   
GROUP BY uid