select uid, days_window, round(examtimes/exam_days_max_cnt*days_window,2) as avg_exam_cnt from ( select uid, examtimes, max((datediff(start_time2,start_time)+1)) as days_window, # 最大间隔 (datediff(max_time3,min_time4)+1) as exam_days_max_cnt # 最大天数

timestampdiff 是会算准确到分秒后，然后取前面的日期，本题需要只算天数

datediff 注意是date1-date2

timestamp diff 是 date2-date1

from( select uid, start_time, lag(start_time,1)over(partition by uid order by start_time DESC) as start_time2, max(start_time)over(partition by uid ) as max_time3, # 最大时间 min(start_time)over(partition by uid ) as min_time4, # 最小时间 # 不要加order by 会影响max,min 排序 count(exam_id)over(partition by uid ) as examtimes # count 不要加order by 会影响count 计数
from exam_record WHERE YEAR(start_time)=2021 # 2021的条件不要落下 order by uid asc,start_time desc )t1 where examtimes>=2 and datediff(max_time3,min_time4)>0 #注意这里的条件 限制每个用户有两天在答题 group by 1,2,4)t2 group by 1,2,3 order by 2 desc ,3 desc