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

select uid
        ,count(start_time) AS exam_complete_cnt /*作答次数*/
from(
    select uid
            ,start_time
            ,submit_time
            ,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) AS ranking
    from exam_record
)t1
where ranking <=3
group by uid
having count(uid) = count(submit_time)
order by exam_complete_cnt desc
        ,uid desc

1、窗口函数用dense_rank()：因为相同月份的排名相同，并且不跳过排名

2、注意窗口函数中order by要将start_time转化为%Y%m的格式，否则每个start_time的排名将会是唯一的。