分解题目

  1. 按每个uid分组，将每个人的答题月份进行降序排列（窗口函数dense_rank）as t1
  2. 筛选出每个人近三个月的答题记录
  3. 筛选出三个月内全部答题记录都是完成状态的人
  4. 计算其三个月内的答题数量
  5. 按照答题数量和uid降序排列

code

with t1 as(select uid, date_format(start_time,"%Y-%m") month, score, 
    dense_rank() over (partition by uid order by date_format(start_time,"%Y-%m") desc) ranking
from exam_record)
select uid, count(score) exam_complete_cnt
from t1
where ranking<=3
group by uid
having sum(if(score is null, 1, 0))=0
order by count(score) desc, uid desc