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

用dense_rank根据年月来排名，这样在同一个月里的日期都可以有相同的名次

select 
temp.uid,
count(temp.submit_time) as exam_complete_count
from 
(select *, 
 dense_rank() over (partition by uid order by date_format(start_time, "%Y-%m") desc) as ranking
 from exam_record) as temp
where temp.ranking <= 3
group by temp.uid
having count(temp.submit_time)=count(temp.start_time)
order by exam_complete_count desc, uid desc