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

select
    tmp1.uid 
    ,count(submit_time) as cnt
from(#按照用户的做题时间新排序
    select
        *
        ,DENSE_RANK() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rk
    from exam_record
    )tmp1
where tmp1.rk<=3 #筛选近满足近三个月的数据
group by tmp1.uid 
    having(count(tmp1.start_time)=count(tmp1.submit_time)) #筛选没有未完成的次数
order by count(submit_time) DESC
        ,uid desc