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

select t1.uid
        ,count(distinct er1.id) AS exam_cnt
        ,count(distinct pr1.id) AS question_cnt
from
(/*先过滤出红名大佬t1*/
    select distinct er.uid
    from exam_record er
    left join user_info ui on er.uid = ui.uid
    left join examination_info ei on er.exam_id = ei.exam_id
    where tag = 'SQL' and difficulty = 'hard' and level = 7
    group by uid
    having avg(score)>80
)t1
left join exam_record er1 on t1.uid = er1.uid and year(er1.submit_time) = 2021
left join practice_record pr1 on t1.uid = pr1.uid and year(pr1.submit_time) = 2021
group by uid
order by count(distinct er1.id) asc
        ,count(distinct pr1.id) desc

1、先筛选出红名大佬，保存在临时表t1中

2、t1分别和试卷表和练习表左连接，按照uid分组，可以统计各红名大佬的答题情况

3、计算答题次数时，用distinct id才可以统计准确，如果用distinct exam_id或者distinct uid，存在用户答题过两次并且满足条件，就会被计算成答题一次，因此用答题记录的唯一id来统计答题次数更准确