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

# 找到 高难度SQL 试卷 得分平均值 大于80 并且是 7级 的 红名大佬
with A as (
select c.uid
from exam_record c
join user_info using(uid)
join examination_info using(exam_id)
where tag = 'SQL'
and difficulty = 'hard'
and level = 7
group by 1
having avg(score) > 80
)
# 统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户
# 试卷总完成次数
,B as (
select uid,count(*)exam_cnt from exam_record
where uid in (select uid from A)
and year(start_time) = 2021
and submit_time is not null
group by 1
)
# 题目总练习次数
,C as (
select uid,count(*)question_cnt from practice_record
where uid in (select uid from B)
and year(submit_time) = 2021
and submit_time is not null
group by 1
)
# 连表 再空值转换
select uid,exam_cnt,ifnull(question_cnt,0)question_cnt
from (
select uid,exam_cnt,question_cnt
from B
left join C using(uid)
)a
order by exam_cnt,question_cnt desc