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

个人实现方式：

-- 2，使用左连接合并练习数过来
select distinct a.uid,exam_cnt,count(question_id)over(partition by pr.uid)as question_cnt
from
-- 1,统计符合条件的用户，以及完成试卷数
(
select er.uid,
sum(if(year(er.submit_time)=2021,1,0))as exam_cnt
from exam_record er
 -- 这里使用左连接，保留原数据（统计是所有完成题目，而非SQL）的同时，从duration可判断SQL的平均分
left join examination_info ei
on ei.tag='SQL' and ei.difficulty='hard' and ei.exam_id=er.exam_id
join user_info ui
on ui.uid=er.uid 
and ui.level=7
group by er.uid 
having sum(if(ei.duration is null,0,er.score))/count(duration)>80
)a
left join practice_record pr
on pr.uid=a.uid and year(submit_time)=2021
order by exam_cnt,question_cnt desc