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

with r as (
    select uid,score,start_time,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as ranking,submit_time
    from exam_record
   
)
select uid,count(score) as exam_complete_cnt
from r
where ranking<=3 and uid not in (
    select uid
    from r
    where ranking<=3 and submit_time is null
)
group by uid


order by exam_complete_cnt desc,uid desc