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

思路：
①根据筛选条件把2021年、SQL困难题目、平均分>80、的7级用户找出来先，形成tb1；
②根据找出来的用户，分别去算出2021年他们的完成试卷数目、完成练习数据，分别形成tb5和tb6；
③因为只保留有试卷完成的用户，所以将tb5左连接tb6即可。

with tb1 as
(select uid 
from user_info 
left join exam_record tb2 using(uid)
left join examination_info tb3 using(exam_id)
where year(tb2.submit_time)=2021 and level=7 and tag='SQL' and difficulty='hard'
group by uid having avg(tb2.score)>80)

select uid, a, if(b is null, 0, b) from
(select tb2.uid, count(tb2.score) a
from tb1
left join exam_record tb2 using(uid)
where year(tb2.submit_time)=2021
group by tb2.uid) tb5

left join

(select tb4.uid, count(tb4.score) b
from tb1
left join practice_record tb4 using(uid)
where year(tb4.submit_time)=2021
group by tb4.uid) tb6
using(uid)
order by a, b desc