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

#请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
#1.sql hard avg(score)>80 level=7 找出uid
select uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where tag='SQL' and difficulty	='hard' and level=7 
group by uid
having avg(score)>80
#2.统计每个用户21年的试卷完成数 题目完成数
select uid,count(submit_time) exam_cnt
from exam_record
where year(submit_time)=2021
group by uid

select uid,count(submit_time) question_cnt
from practice_record
where year(submit_time)=2021
group by uid

#3.组合以上，统计这些uid 2021年的 试卷完成次数 题目练习次数
select uid,COALESCE(exam_cnt,0),COALESCE(question_cnt,0)
from
(select uid,count(submit_time) exam_cnt
from exam_record
where year(submit_time)=2021
group by uid) data1
left join 
(select uid,count(submit_time) question_cnt
from practice_record
where year(submit_time)=2021
group by uid) data2 using(uid)
where uid in (select uid
                from exam_record
                join examination_info using(exam_id)
                join user_info using(uid)
                where tag='SQL' and difficulty	='hard' and level=7 
                group by uid
                having avg(score)>80)
order by exam_cnt,question_cnt desc