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

# 查询各用户每个月的试卷作答记录数和试卷完成数以及每个月的时间排序
select uid, date_format(start_time, '%Y%m') s_month,
count(start_time) s_num, count(submit_time) f_num, 
row_number()over(partition by uid order by date_format(start_time, '%Y%m') desc) rank_num
from exam_record
group by uid, date_format(start_time, '%Y%m')
# 查询各用户有试卷作答记录的近三个月中没有试卷是未完成状态的用户的试卷作答完成数
select uid, sum(f_num) exam_complete_cnt
from (
    select uid, date_format(start_time, '%Y%m') s_month,
    count(start_time) s_num, count(submit_time) f_num, 
    row_number()over(partition by uid order by date_format(start_time, '%Y%m') desc) rank_num
    from exam_record
    group by uid, date_format(start_time, '%Y%m')
) k1
where rank_num in (1,2,3)
group by uid
having sum(s_num)-sum(f_num) = 0     # 筛选出近三个月中没有试卷未完成的用户
order by exam_complete_cnt desc, uid desc;

having中能放利用聚合函数的分组条件，因此将近三个月没有试卷未完成的用户筛选条件放在这里。