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

with u1 as 
    (select ui.uid
    from user_info ui 
    inner join exam_record er on er.uid= ui.uid
    left join examination_info ei on ei.exam_id = er.exam_id 
    where ui.level=7 and ei.tag='SQL' and difficulty='hard' 
    and submit_time is not null and score is not null
    group by ui.uid,level
    having avg(score)>80),

u2 as   
    (select ui.uid,
        count(er.submit_time) exam_cnt
    from user_info ui 
    inner join exam_record er on er.uid= ui.uid
    inner join u1 on u1.uid = ui.uid
    where year(er.submit_time)=2021
    group by ui.uid
    having exam_cnt>0),
    

u3 as 
    (select ui.uid,count(pr.id) question_cnt
    from user_info ui 
    inner join practice_record pr on pr.uid= ui.uid
    where year(pr.submit_time)=2021
    group by 1)


select u2.uid,u2.exam_cnt,ifnull(u3.question_cnt,0) question_cnt
from u2 left join u3 on u3.uid = u2.uid
order by exam_cnt asc,question_cnt desc