分析

• 排序方式——dense_rank()
• 筛选范围——where rn<=3
• 筛选对象——全部提交 count(start_time)=count(submit_time)

提交：

select uid,exam_start_cnt as exam_complete_cnt
from
    (select uid, count(start_time) as exam_start_cnt,
    count(submit_time) as exam_submit_cnt
     from 
         (select *,date_format(start_time,'%Y%m') as month0,
          dense_rank() over(partition by uid order by
                            date_format(start_time,'%Y%m') desc)as rn
         from exam_record) as q1
    where rn<=3
    group by uid
    ) as q2
where exam_start_cnt=exam_submit_cnt
order by exam_complete_cnt desc,uid desc

改进

• 筛选对象——全部提交 count(score)=count(uid) 直接放在最外层的having筛选中

select uid,count(start_time) as exam_complete_cnt
from
   (select *,date_format(start_time,'%Y%m') as month0,
          dense_rank() over(partition by uid order by
                            date_format(start_time,'%Y%m') desc)as rn
    from exam_record) as q1
where rn<=3
group by uid
having count(start_time)=count(submit_time)
order by exam_complete_cnt desc,uid desc