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

#找到每个人 近三个有试卷作答记录的月份 中 没有试卷是未完成状态 的用户的 试卷作答完成数，按试卷完成数和用户ID降序排名。
with data2 as
(select *
from(select uid,date(start_time) start_time,date(submit_time) submit_time,
       dense_rank()OVER(PARTITION BY uid ORDER BY date_format(start_time,'%Y%m') desc) ranking
from exam_record
order by uid,start_time) data1
where ranking<4)

select uid,count(uid) 	exam_complete_cnt
from data2
where uid in
    (select uid
    from data2
    group by uid
    having sum(if(submit_time is null,1,0))=0)
group by uid 
order by exam_complete_cnt desc,uid desc