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

select uid,
       count(score) as exam_complete_cnt
from(
    select uid, start_time,submit_time, score,
           dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
    from exam_record
) recent_table
where recent_months <= 3 
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc

本题主要考核的是dense_rank()over(partition by uid date_format(start_time, '%Y%m') desc),近三个月的意思是最靠近现在的三个月可以用desc来表示，having count(score) = count(uid)来表示分数的量和uid的数量一样多，也就是没有null的情况。