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

未完成试卷为什么要用“having count(start_time)=count(score)” 是关键，因为如果判断完成时间为空或分数为空就剔除，那会导致如近三个月有一个月份有两份试卷，一份未完成、一份已完成，该用户直接被剔除。

select uid,
count(score)   exam_complete_cnt

from (select uid,start_time,score, 
dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) recent_months
from exam_record ) er
where recent_months<=3

group by uid
having count(start_time)=count(score)
order by exam_complete_cnt desc,uid desc