明确题意：

找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷完成数和题目练习数; 结果按试卷完成数升序，按题目练习数降序。

问题分解：

• 找出高难度SQL试卷得分平均值大于80分的7级用户，生成子表t_user_id：
  • 内连接试卷作答表、试卷信息表、用户信息表：exam_record JOIN examination_info USING(exam_id) JOIN user_info USING(uid)
  • 筛选出目标用户：WHERE difficulty='hard' and tag='SQL' and level=7
  • 按用户分组：GROUP BY uid
  • 筛选平均分大于80的分组（用户）：HAVING AVG(score)>80
• 统计每个用户2021年的试卷完成数，生成子表t_exam_cnt：
  • 筛选作答时间和已完成：WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
  • 按用户分组：GROUP BY uid
  • 统计试卷完成数：count(exam_id) as exam_cnt
• 统计每个用户2021年的试题作答数，生成子表t_question_cnt：
  • 筛选作答时间：WHERE YEAR(submit_time)=2021
  • 按用户分组：GROUP BY uid
  • 统计试题作答数：count(question_id) as question_cnt
• 关联三张表：
  • 内连接t_user_id和t_exam_cnt，因为用户属性条件必须满足，题目又要求了只输出有试卷完成记录的用户：t_user_id JOIN t_exam_cnt USING(uid)
  • 左连接t_question_cnt：因为只要满足上面条件的结果，即使无试题作答记录也输出：LEFT JOIN t_question_cnt USING(uid)
• 特殊处理试题作答数可能为0的情况（此时关联结果中试题作答数为NULL）：IFNULL(question_cnt, 0) as question_cnt

细节问题：

• 表头重命名：as
• 按试卷完成数升序、题目练习数降序：ORDER BY exam_cnt, question_cnt desc

完整代码：

SELECT uid, exam_cnt, IFNULL(question_cnt, 0) as question_cnt
FROM (
    SELECT uid
    FROM exam_record
    JOIN examination_info USING(exam_id)
    JOIN user_info USING(uid)
    WHERE difficulty='hard' and tag='SQL' and `level`=7
    GROUP BY uid
    HAVING AVG(score)>80
) as t_user_id
JOIN (
    SELECT uid, count(exam_id) as exam_cnt
    FROM exam_record
    WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
    GROUP BY uid
) as t_exam_cnt
USING(uid)
LEFT JOIN (
    SELECT uid, count(question_id) as question_cnt
    FROM practice_record WHERE YEAR(submit_time)=2021
    GROUP BY uid
) as t_question_cnt
USING(uid)
ORDER BY exam_cnt, question_cnt desc;