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

select
table1.uid
,exam_cnt
,question_cnt
from
(
    select
    uid
    ,count(exam_id) exam_cnt
    from 
    (
        select
        *
        from exam_record
        where substring(submit_time,1,4) = '2021'
    ) t7
    where uid in
    (
        select
        t1.uid uid
        from exam_record t1 left join examination_info t2
        on t1.exam_id = t2.exam_id
        left join user_info t3
        on t1.uid = t3.uid
        where tag = 'SQL'
        and difficulty = 'hard'
        and level = 7
        and t1.submit_time is not null
        and substring(start_time,1,4) = '2021'
        group by 1
        having avg(t1.score) > 80
    )
    group by 1
) table1

left join

(
    select
    t5.uid
    ,count(question_id) question_cnt
    from user_info t5 left join 
    (
        select
        *
        from practice_record
        where substring(submit_time,1,4) = '2021'
    ) t6
    on t5.uid = t6.uid
    where t5.uid in
    (
        select
        t1.uid uid
        from exam_record t1 left join examination_info t2
        on t1.exam_id = t2.exam_id
        left join user_info t3
        on t1.uid = t3.uid
        where tag = 'SQL'
        and difficulty = 'hard'
        and level = 7
        and t1.submit_time is not null
        and substring(start_time,1,4) = '2021'
        group by 1
        having avg(t1.score) > 80
    )
    group by 1
) table2
on table1.uid = table2.uid
order by exam_cnt,question_cnt desc