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

# 高难度SQL试卷
# 得分平均值大于80并且是7级的用户
# 2021年试卷总完成次数和题目总练习次数
# 只保留2021年有试卷完成记录的用户
# 结果按试卷完成数升序，按题目练习数降序

SELECT a.uid,
       COUNT(DISTINCT r1.id) AS exam_cnt,
       COUNT(DISTINCT p.id) AS question_cnt
FROM(
    SELECT r.uid
    FROM exam_record AS r 
    LEFT JOIN examination_info AS i ON r.exam_id=i.exam_id
    LEFT JOIN user_info AS u ON r.uid=u.uid
    WHERE i.tag='SQL' AND i.difficulty='hard'
    AND u.level=7
    GROUP BY r.uid
    HAVING AVG(r.score)>80
    ) AS a
LEFT JOIN exam_record AS r1 ON a.uid=r1.uid AND YEAR(r1.submit_time)='2021'
LEFT JOIN practice_record AS p ON a.uid=p.uid AND YEAR(p.submit_time)='2021'
GROUP BY a.uid
ORDER BY exam_cnt, question_cnt DESC
;