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

-- 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷完成数和题目练习数， 只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
-- 先找到红名大佬uid,再统计试卷完成数和题目练习数
-- 1、用户得分平均值,分组求平均数。先关联查询，再过滤，再分组求平均数，再过滤
2、统计用户试卷完成数
3、统计用户题目练习数
4、左连接查询
SELECT 
    d.uid,
    IF(e.exam_cnt IS NULL, 0, e.exam_cnt) exam_cnt,
    IF(f.question_cnt IS NULL,
        0,
        f.question_cnt) question_cnt
FROM
    (SELECT 
        a.uid
    FROM
        exam_record a
    INNER JOIN examination_info b ON a.exam_id = b.exam_id
    INNER JOIN user_info c ON a.uid = c.uid
    WHERE
        b.tag = 'SQL' AND b.difficulty = 'hard'
            AND c.level = 7
    GROUP BY uid
    HAVING AVG(a.score) > 80) d
        LEFT JOIN
    (SELECT 
        uid, COUNT(DISTINCT exam_id) exam_cnt
    FROM
        exam_record
    WHERE
        YEAR(submit_time) = 2021
    GROUP BY uid) e ON d.uid = e.uid
        LEFT JOIN
    (SELECT 
        uid, COUNT(question_id) question_cnt
    FROM
        practice_record
    WHERE
        YEAR(submit_time) = 2021
    GROUP BY uid) f ON d.uid = f.uid
ORDER BY exam_cnt ASC , question_cnt DESC;