一、明确需求

• 高难度SQL试卷得分平均值>80 and level = 7 的用户， 统计其2021年试卷总完成次数和题目总练习次数（剔除没有试卷完成记录的用户）
• 按试卷完成数升序

二、问题分解

1. 作答表左连信息表，左连用户信息表，选择筛选条件，获得符合条件用户uid名单-表1
2. 表1连试卷作答表，选择时间2021，分组计算，剔除没有完成记录用户得到表2
3. 表1连接题目练习表，选择时间2021，分组计算得到表3
4. 表2左连表3，排序

三、代码实现

with 
# 试卷作答表 左连 试卷信息表 左连用户信息表 获得用户uid名单
t1 as 
(
select a.uid
from exam_record a 
    left join examination_info b on a.exam_id = b.exam_id
    left join user_info c on a.uid = c.uid
where b.tag = 'SQL' and b.difficulty = 'hard' and level=7
group by a.uid 
having avg(a.score) > 80)

# t1表连练习记录表，统计完成试卷次数
, t2 as
(
select a.uid,count(b.submit_time) exam_cnt
from t1 a left join exam_record b
    on a.uid = b.uid 
where year(b.submit_time) = '2021'
group by a.uid 
having count(b.submit_time) > 0
)

# t1表左连题目联系表，统计题目练习次数
, t3 as
(
select a.uid
    , count(b.submit_time) question_cnt
from t1 a left join practice_record b
    on a.uid = b.uid 
where year(b.submit_time) = '2021'
group by a.uid)

# t2左连t3，对question_cnt取ifnull()函数操作，最后排序
select *
from (
select distinct t2.*,ifnull(t3.question_cnt,0) question_cnt
from t2 left join t3 on t2.uid = t3.uid )x
order by exam_cnt asc,question_cnt desc