题解 | 满足条件的用户的试卷完成数和题目练习数

select
    a.uid uid,
    exam_cnt,
    if (question_cnt is null, 0, question_cnt) question_cnt
from
    (
        select
            ui.uid uid,
            count(distinct er.exam_id) exam_cnt
        from
            test.user_info ui
            left join test.exam_record er on ui.uid = er.uid
            left join test.examination_info ei on er.exam_id = ei.exam_id
        where
            ui.uid in (
                select
                    ui.uid
                from
                    test.user_info ui
                    left join test.exam_record er on ui.uid = er.uid
                    left join test.examination_info ei on er.exam_id = ei.exam_id
                where
                    tag = 'SQL'
                    and difficulty = 'hard'
                    and level = 7
                    and year (start_time) = 2021
                group by
                    ui.uid
                having
                    avg(score) > 80
            )
            and year (start_time) = 2021
        group by
            ui.uid
    ) a
    left join (
        select
            uid,
            count(question_id) question_cnt
        from
            test.practice_record
        where
            uid in (
                select
                    ui.uid
                from
                    test.user_info ui
                    left join test.exam_record er on ui.uid = er.uid
                    left join test.examination_info ei on er.exam_id = ei.exam_id
                where
                    tag = 'SQL'
                    and difficulty = 'hard'
                    and level = 7
                    and year (start_time) = 2021
                group by
                    ui.uid
                having
                    avg(score) > 80
            )
            and year (submit_time) = 2021
        group by
            uid
    ) b on a.uid = b.uid
order by
    exam_cnt asc,
    question_cnt desc