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

with
    -- 找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
    target_user as (
        select
            uid
        from
            (
                select
                    uid,
                    avg(score) as avg_score
                from
                    exam_record
                where
                    exam_id in (
                        select
                            exam_id
                        from
                            examination_info
                        where
                            difficulty = 'hard'
                            and tag = 'SQL'
                    )
                    and uid in (
                        select
                            uid
                        from
                            user_info
                        where
                            level = 7
                    )
                group by
                    uid
                having
                    avg_score > 80
            ) as t1
    ),
    --    统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
    exam_cnt_temp as (
        select
            uid,
            count(submit_time) as exam_cnt
        from
            target_user
            left join exam_record using (uid)
        where
            uid in (
                select
                    uid
                from
                    target_user
            )
            and year(submit_time) = 2021
        group by
            uid
    ),
    question_cnt_temp as (
        SELECT
            t.uid,
            COUNT(p.submit_time) AS question_cnt
        FROM
            target_user t
            LEFT JOIN practice_record p ON t.uid = p.uid
            AND YEAR(p.submit_time) = 2021
        GROUP BY
            t.uid
    )
select
    *
from
    exam_cnt_temp
    join question_cnt_temp using (uid)
order by
    exam_cnt asc,
    question_cnt desc