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

select
    uid,
    count(month (start_time)) exam_complete_cnt
from
    (
        select
            uid,
            exam_id,
            start_time,
            submit_time,
            dense_rank() over (
                partition by
                    uid
                order by
                    date_format (start_time, '%Y%m') desc
            ) raking
        from
            exam_record
    ) w
where
    raking <= 3
group by
    uid
having
    sum(if (submit_time is null, 1, 0)) = 0
order by
    exam_complete_cnt desc,
    uid desc