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

-- 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序
with t as(
    select uid,exam_id,avg(score) as sc
    from exam_record 
    where year(start_time) = 2021
    group by uid,exam_id
),
a as (
    select t.uid,count(ifnull(t.exam_id,0)) as exam_cnt
from t
left join examination_info i
on t.exam_id = i.exam_id 
where t.uid in (
    select t.uid
    from t 
    left join examination_info f
    on t.exam_id = f.exam_id 
    where f.tag = 'SQL' and f.difficulty='hard' and t.sc > 80
) and t.uid in (
    select uid from user_info where level = 7
)
group by t.uid
)
select a.uid,exam_cnt,ifnull(r.question,0) as question_cnt
from a 
left join (select uid,count(question_id) as question
    from practice_record 
    where year(submit_time) =2021
    group by uid) r
on a.uid = r.uid
order by exam_cnt asc,question_cnt desc;