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

#7级大佬
with t as (
    select u.uid,avg(er.score) as avg_score

    from user_info u
    left join exam_record er
    on u.uid=er.uid
    left join practice_record p
    on u.uid = p.uid
    join examination_info e
    on e.exam_id = er.exam_id

    where level =7
    and tag='SQL'
    and difficulty ='hard'

    group by u.uid
    having avg_score>80
)

select t.uid,
count(distinct er.id) as exam_cnt,
count(distinct p.id) as question_cnt

from  t
left join exam_record er on t.uid = er.uid and substr(er.submit_time,1,4)=2021
left join practice_record p on t.uid = p.uid and substr(p.submit_time,1,4)=2021

group by t.uid
order by exam_cnt, question_cnt desc

根据大佬，使用id进行计数