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

select 
    t1.uid
    , t1.exam_cnt
    , ifnull(t2.question_cnt, 0) question_cnt
from  
	-- 考试次数
    (
        select uid, count(submit_time) exam_cnt
        from exam_record
        where year(submit_time) = 2021
        group by uid
    ) t1
    left join
	-- 练习次数
    (
        select uid, count(submit_time) question_cnt
        from practice_record
        where year(submit_time) = 2021
        group by uid
    ) t2
    on t1.uid = t2.uid
where 
	-- 筛选uid
    t1.uid in (
        select 
            a1.uid
        from 
            exam_record a1
            left join examination_info a2
            on a1.exam_id = a2.exam_id
            left join user_info a3
            on a1.uid = a3.uid
        where 
            a2.tag = 'SQL'
            and a2.difficulty = 'hard'
            and a3.level = 7
        group by 
            a1.uid
        having 
            avg(a1.score) > 80
    )
order by 
    exam_cnt asc
    , question_cnt desc
;