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

SELECT
    temp.uid,
    COUNT(DISTINCT er.id) AS exam_cnt,
    COUNT(DISTINCT pr.id) AS question_cnt
FROM (
    SELECT uid
    FROM exam_record
    JOIN user_info USING (uid)
    JOIN examination_info USING (exam_id)
    WHERE tag = 'SQL' and difficulty = 'hard' and `level` = 7
    GROUP BY uid
    HAVING AVG(score) > 80
) AS temp  # 高难度SQL试卷得分平均值大于80并且是7级的红名大佬的uid
LEFT JOIN exam_record er
    ON temp.uid = er.uid AND YEAR(er.submit_time) = 2021  # 仅保留2021年的提交记录
LEFT JOIN practice_record pr
    ON temp.uid = pr.uid AND YEAR(pr.submit_time) = 2021  # 仅保留2021年的提交记录
GROUP BY uid
HAVING exam_cnt > 0  # 仅保留有试卷完成记录的用户
ORDER BY exam_cnt, question_cnt DESC