select uid,
max(days_w) as days_window,
round(count(exam_id)/(timestampdiff(day,min(dt),max(dt))+1)*max(days_w),2) as avg_exam_cnt
from(
    select b.uid,exam_id,
    dt,
    timestampdiff(day,lag_t,dt)+1 as days_w
    from(
        select uid,
        exam_id,
        date(start_time) as dt,
        lag(date(start_time)) over(partition by uid order by start_time ) as lag_t
        from exam_record 
        where uid in (select uid from exam_record
                    where year(start_time)=2021
                    group by uid 
                    having count(distinct date(start_time)) >=2)
                    and year(start_time) =2021
        order by uid
    )b
)c
group by uid
order by days_window desc,avg_exam_cnt desc

做这道题看不懂题目的话一定要看下面的用例解释，该年连续两次作答试卷的最大时间窗days_window 意思是每两次答题之间的间隔天数取最大，比如用户1006分别在20210901、20210906、20210907作答过3次试卷，第一个时间窗为6天（1号到6号），第二个时间窗为2天（6号到7号），因此最大时间窗为6号

第一步：求最初始的限制条件--2021年至少有两天作答过试卷的人

select uid from exam_record
                    where year(start_time)=2021
                    group by uid 
                    having count(distinct date(start_time)) >=2

第二步：求最大时间窗

这里通过lag实现，lag 相当于把每个uid对应的start_time往后了一行，且重新生成了一列，我把它命名为lag_w，这样就能在外层用timestampdiff(day,lag_t,dt)+1计算出时间窗，

之后在最外层用聚合函数max求出最大时间窗

lag(date(start_time)) over(partition by uid order by start_time ) as lag_t

第三步：求根据该年的历史规律他在days_window天里平均会做多少套试卷

我的想法是先求平均每天做几张（命名为days_w），再求days_window内会做几张(命名为days_week)。

对于days_w，题目解释比较清晰，用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张

分别求分子和分母，这样思路清晰一些。

  • 分子是从最小日期到最大日期，这个uid一共做了几张卷子，所以其实就是求每个uid下有几个exam_id，可以在最外层用count(exam_id)实现
  • 分母是最大日期到最小日期一共有几天，同样在最外层通过(timestampdiff(day,min(dt),max(dt))+1函数进行实现
  • 用分子/分母，得到days_w。即
count(exam_id)/(timestampdiff(day,min(dt),max(dt))+1)

然后求days_week，这个很简单直接用之前求的最大时间窗×days_w即可

round(count(exam_id)/(timestampdiff(day,min(dt),max(dt))+1)*max(days_w),2)

由此差不多做完整道题，看不懂题目的时候，看解释有比较大的帮助，大问题不知道怎么算的时候，可以先拆分成小问题，这样就能做出来了。