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

# 结构化拆解问题
SELECT t1.uid, t1.exam_cnt, IFNULL(t2.question_cnt, 0) AS question_cnt
FROM (
    SELECT er.uid, COUNT(er.exam_id) AS exam_cnt
    FROM exam_record AS er
    WHERE YEAR(submit_time) = 2021
    GROUP BY er.uid) AS t1 # 先查询每个用户在2021年做多少试卷
LEFT OUTER JOIN 
    (
    SELECT pr.uid, COUNT(pr.question_id) AS question_cnt
    FROM practice_record AS pr
    WHERE YEAR(submit_time) = 2021
    GROUP BY pr.uid) AS t2 # 再查询每个用户在2021年刷了多少题
ON t1.uid = t2.uid
WHERE t1.uid IN (
    SELECT er.uid
    FROM exam_record AS er
    INNER JOIN examination_info AS ei 
    ON er.exam_id = ei.exam_id
    LEFT OUTER JOIN user_info AS u
    ON er.uid = u.uid
    WHERE u.level = 7 AND ei.tag = 'SQL' AND ei.difficulty = 'hard'
    GROUP BY er.uid
    HAVING AVG(er.score) > 80) # 作为条件筛选

ORDER BY exam_cnt, question_cnt DESC;