题解 | #近三个月未完成试卷数为0的用户完成情况#

本题思路首先是要查询出近三个有作答记录月份没有出现试题未完成记录的用户，而后根据查询到的用户进行子查询得到进行最后查询所需的数据。本题的要点如下：

1. 利用dense_rank()over()函数筛选出用户作达记录中近三个月份的数据；
2. 利用group by函数进行聚合，计算start_time字段以及submit_time字段的个数是否相等作为判断是否有未完成记录的依据；
3. 利用上述两步得到符合要求的用户，将其作为子查询的条件筛选得到这些用户近三个月的作达记录并进行统计即可。

select
  uid,
  count(start_time) exam_complete_cnt
from
  (select *,dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) rk
    from exam_record) t 
where
  rk <= 3
  and uid in (select uid from
      				(select uid,rk,start_time start,submit_time submit
        					from
          						(select *,dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) rk
            						from exam_record) t where rk <= 3) t1
    			group by uid 
                having count(start) = count(submit))
group by uid
order by exam_complete_cnt desc,uid desc