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

with dalao as(
    select uid
    from exam_record
    where exam_id in (select exam_id from examination_info where tag = 'SQL')
        and uid in (select uid from user_info where level >= 7)
    group by uid
    having avg(score) > 80
),
cntexam as (
    select uid, count(submit_time) exam_cnt
    from exam_record
    where uid in (select uid from dalao) and year(submit_time) = '2021'
    group by uid
),
cntpractice as (
    select uid, count(submit_time) question_cnt
    from practice_record
    where uid in (select uid from dalao) and year(submit_time) = '2021'
    group by uid
)

select e.uid, e.exam_cnt exam_cnt, if(p.question_cnt is not null, p.question_cnt, 0) question_cnt
from cntexam e
left join cntpractice p
on e.uid = p.uid
order by exam_cnt, question_cnt desc

请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬——筛出符合条件的dalao，之后用where in筛人就行

统计他们的2021年试卷总完成次数和题目总练习次数——由于两个列基于不同的表，怎么join都有点麻烦，不如将后面两列直接拆成两个表entexam和cntpractice（随便起的名），分别计算再组合。这里注意只保留2021年有试卷exam完成记录的用户，故组合的的时候要注意question_cnt要加一个if判断，空值用0补充。

最后结果按试卷完成数升序，按题目练习数降序，输出即可