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

构建临时表：筛选满足的用户信息 + 用户完成试卷数 + 用户完成试题数

最后要注意需要 左连接 + ifnull的处理

with t1 as (
#先筛选出满足的用户
    select
        ui.uid
    from examination_info ei join exam_record er 
        on ei.exam_id=er.exam_id and ei.tag='SQL' and ei.difficulty='hard'
    join user_info ui on ui.uid=er.uid and ui.level=7
    where substr(er.submit_time,1,10) between '2021-01-01' and '2021-12-31'
    group by ui.uid
    having(avg(er.score)>80)
)
,t2 as (
#分别统计满足条件用户的试卷数和答题数
    #试卷数
    select
        t1.uid
        ,count(er.exam_id) as exam_cnt
    from t1 join exam_record er using(uid)
    where substr(er.submit_time,1,10) between '2021-01-01' and '2021-12-31'
    group by t1.uid
)
,t3 as (
#分别统计满足条件用户的试卷数和答题数
    #题目数
        select
        t1.uid
        ,count(pr.question_id) as question_cnt
    from t1 join practice_record pr using(uid)
    where substr(pr.submit_time,1,10) between '2021-01-01' and '2021-12-31'
    group by t1.uid
)
SELECT
    t2.uid
    ,t2.exam_cnt
    ,ifnull(t3.question_cnt,0) as question_cnt
from t2 left join t3 using(uid)
order by t2.exam_cnt,question_cnt desc