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

select 
uid,sum(num1) as exam_complete_cnt
from (
        select uid,date_format(start_time,'%Y%m') as month_d , 
        count(start_time) as num1 ,count(submit_time) as num2
        ,row_number() over( partition by uid order by 
        date_format(start_time,'%Y%m')  desc ) as ranking
        from exam_record
        group by uid,date_format(start_time,'%Y%m'))t
where  ranking <=3
group by uid having sum(num1)=sum(num2)
order by exam_complete_cnt desc,uid desc

本来是想的这个方式，看了讨论区在用 dense_rank 感觉妙呀。

使用dense_rank直接就可以把月份出现的次数即位置求出来

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