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

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

要用dense_rank()而不是row_unmber()，因为最近三个月的记录相同月份也要记录在内。