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

select
    a.uid,
    count(submit_time) as exam_complete_cnt
from(  
    #step1:找出近三个月的做题记录
    select
        uid,
        start_time,
        submit_time,
        dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) as rk
    from
        exam_record
)as a
where 
    rk<=3
group by uid
having count(submit_time) = count(start_time)
order by  exam_complete_cnt desc,uid desc