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

思路：

1. 每个人近三个有试卷作答记录的月份

2. 过滤：三个月中试卷均完成的用户 --sum(if(submit_time is null,1,0))=0, group by uid

# 思路清晰版
with t as (
    select uid, 
        date_format(start_time,"%Y%m") ym,
        dense_rank() over (partition by uid order by date_format(start_time,"%Y%m") DESC) mrank
    from exam_record
)

select 
    uid,
    count(submit_time) exam_complete_cnt
from exam_record
where (uid, date_format(start_time,"%Y%m")) in (select uid, ym from t where mrank <= 3)
group by uid
having sum(if(submit_time is null,1,0))=0
order by exam_complete_cnt DESC, uid DESC

# 缩减版
select 
    uid,
    count(submit_time) exam_complete_cnt
from(
    select uid, submit_time,
        date_format(start_time,"%Y%m") ym,
        dense_rank() over (partition by uid order by date_format(start_time,"%Y%m") DESC) mrank
    from exam_record
) a where mrank <= 3
group by uid
having sum(if(submit_time is null,1,0))=0
order by exam_complete_cnt DESC, uid DESC