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

with temp as (select ui.uid
              from exam_record er
                       inner join examination_info ei on er.exam_id = ei.exam_id
                       inner join user_info ui on er.uid = ui.uid
              where ui.level = 7
                and ei.tag = 'SQL'
                and ei.difficulty = 'hard'
              group by ui.uid
              having avg(score) > 80)
select t.uid
     , count(distinct er_1.exam_id) exam_cnt
     , count(distinct pr.submit_time)   question_cnt
from temp t
         left join exam_record er_1 on t.uid = er_1.uid and year(er_1.submit_time) = 2021
         left join practice_record pr on t.uid = pr.uid and year(pr.submit_time) = 2021
group by t.uid
order by exam_cnt, question_cnt desc;