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

1. row_number() ,rank(),dense_rank() 三个函数的用法是不同的，row_number() 无论考试的分数是否相同 ，都会对考试的结果进行排序，并按照顺序排序，如1，2，3，4，5；rank() 按照考试的结果排序，相同的分数按照相同的结果排序，不同的分数进行跳序排列，如 1，2，2，4，5，5，5，8……；dense_rank() 按照考试的结果排序，相同的分数按照相同的结果排序，不同的分数依次排序，如1，2，3，4，4，5，5，6 


/* 
select uid,
       count(score) as exam_complete_cnt
from(
    select uid, start_time, score,
           dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
    from exam_record
) recent_table
where recent_months <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc ;
*/  
 
select uid,
       count(start_time) exam_complete_cnt 
  from (
  select uid,
         start_time ,
         submit_time ,
         dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) rn  
    from exam_record ER 
    ) A 
    where rn <= 3 
    group by uid 
    having count(start_time) = count(submit_time) 
    order by exam_complete_cnt desc,uid desc ;