【牛客】SQL134 满足条件的用户的试卷完成数和题目练习数

描述

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

示例数据输出如下：

解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；1001完成了1次试卷1001，练习了2次题目；1003完成了2次试卷9001、9002，未练习题目（因此计数为0）

with cte1 as(
    select uid
    from
    exam_record left join examination_info using(exam_id)
    left join user_info using(uid)
    where difficulty='hard' and tag='SQL' and level=7
    group by uid
    having avg(score)>80
),
cte2 as(
    select uid,count(exam_id) as exam_cnt
    from
    cte1 left join  exam_record using(uid)
    where left(submit_time,4)=2021
    group by uid
),
cte3 as(
    select uid,count(question_id) as question_cnt
    from
    cte1 left join practice_record using(uid)
    where date_format(submit_time,'%Y')=2021
    group by uid
)
select uid,exam_cnt,ifnull(question_cnt,0)
from
cte2 left join cte3 using(uid)
order by exam_cnt,question_cnt desc