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

# 每个人：group by uid
# 近三个有试卷作答记录的月份:排序试卷作答时间，取出前三个不同的月，统计该三个的试卷做答数和完成数。
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 temp_rank 
from exam_record ) as recent_table
where temp_rank<4
group by uid 
having count(score)=count(start_time)
order by exam_complete_cnt desc, uid desc

知识点：dense_rank() 开窗函数，与row_number的区别是：该函数排序，相同的值排序数一样，序号不间断，比如：1123