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

select
    uid,
    count(exam_id) 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
            ) 排名
        from
            test.exam_record
    ) a
where
    排名 <= 3
    and uid not in (
        select distinct
            判断
        from
            (
                select
                    if (submit_time is null, uid, 0) 判断
                from
                    (
                        select
                            uid,
                            exam_id,
                            start_time,
                            submit_time,
                            dense_rank() over (
                                partition by
                                    uid
                                order by
                                    date_format (start_time, '%Y%m') desc
                            ) 排名
                        from
                            test.exam_record
                    ) a
                where
                    排名 <= 3
            ) b
        where
            b.判断 != 0
    )
group by
    uid
order by
    exam_complete_cnt desc,
    uid desc