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

select uid,exam_cnt,
#左连接输出有完成书卷记录的用户
if(question_cnt>=0,question_cnt,0) question_cnt 
from 
# 2021年用户完成试卷的次数
(select uid,count(submit_time) exam_cnt
from exam_record
where year(submit_time)=2021 and length(submit_time	)>0
and uid in
# 计算高难度SQL试卷得分平均值大于80并且是7级的用户
(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) 
group by uid)t1
left join 
# 2021年用户及用户题目练习次数
(select uid,count(submit_time) question_cnt
from practice_record where year(submit_time)=2021
group by uid)t3 using(uid)
order by 2,3 desc