with er_2 as 
(select ui.uid as uid, level, tag, difficulty,
er.submit_time as submit_time,er.score as score
from user_info ui
join exam_record er on er.uid = ui.uid
join examination_info ei on ei.exam_id = er.exam_id
),

sql_master as 
(select uid,avg(score) from er_2
where year(submit_time) = 2021 and level =7 and difficulty = 'hard' and tag = 'SQL' 
group by uid
having avg(score) > 80),

exam_cnt_result as (
select er_2.uid as uid,count(er_2.uid) as exam_cnt from er_2
join sql_master sm on sm.uid = er_2.uid
where score is not null and year(submit_time) = 2021
group by er_2.uid),

question_cnt_result as (
select ecr.uid as uid,
count(ecr.uid) as question_cnt
from 
(select * from practice_record 
where year(submit_time) = 2021 ) pr
join exam_cnt_result ecr on ecr.uid = pr.uid
group by ecr.uid)

select ecr.uid,exam_cnt,ifnull(question_cnt,0) as question_cnt from exam_cnt_result ecr
left join question_cnt_result qcr 
on ecr.uid = qcr.uid
order by exam_cnt, question_cnt desc