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

1. 首先统计出来，高难度SQL试卷的平均分数大于80分并且属于7级红名大佬的uid有那些？然后使用UID临时表分别关联
exam_record 表得到 大佬UID所对应的当年（2021）的试卷提交量，左外关联表practice_record 得到大佬UID所对应当年（2021）年的题目提交数。

select A1.uid,
       A1.exam_cnt,
       A2.question_cnt 
  from 
   (select A.uid, 
          count(ER.score) exam_cnt 
     from (
   #得到 高难度SQL试卷得分平均值大于80分并且属于7级的红名大佬 
   select UI.uid 
     from exam_record ER 
     join user_info UI using(uid) 
     join examination_info EI using(exam_id) 
     where EI.tag = 'SQL' 
       and EI.difficulty = 'hard' 
       and UI.level = 7 
     group by ER.uid 
     having sum(ER.score)/count(ER.score) > 80 
     ) A join exam_record ER using(uid) 
     where year(ER.submit_time) = 2021 
     group by ER.uid 
   ) A1 join 
    ( select A.uid,
             count(PR.submit_time) question_cnt 
        from (
        #得到 高难度SQL试卷得分平均值大于80分并且属于7级的红名大佬 
        select UI.uid 
            from exam_record ER join user_info UI using(uid) 
            join examination_info EI using(exam_id) 
            where EI.tag = 'SQL' 
            and EI.difficulty = 'hard' 
            and UI.level = 7 
            group by ER.uid 
            having sum(ER.score)/count(ER.score) > 80 
        ) A left join practice_record PR on A.uid = PR.uid and year(PR.submit_time) = 2021 
        group by A.uid 
        ) A2 using(uid)
     order by A1.exam_cnt,A2.question_cnt desc ;