题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
http://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
SELECT uid ,
MAX(DATEDIFF(next_time,start_time))+1 AS days_window,
ROUND( COUNT(exam_id)/(DATEDIFF(MAX(start_time),MIN(start_time))+1) * (MAX(DATEDIFF(next_time,start_time))+1),2) AS avg_exam_cnt
FROM
(
SELECT uid, exam_id,start_time,lead(start_time)over(PARTITION BY uid ORDER BY start_time) AS next_time
FROM exam_record
WHERE YEAR(start_time) = 2021
)AS tb1
GROUP BY uid
HAVING COUNT(DISTINCT DATE(start_time))>=2
ORDER BY days_window DESC,avg_exam_cnt DESC;
