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

/*
 #高难度SQL试卷
 #得分平均值大于80
 #并且是7级的红名大佬，
 #2021年
 -------------------------------
 统计他们的:
 #试卷总完成次数
 #题目总练习次数
 #只保留2021年有试卷完成记录的用户。
 #结果按试卷完成数升序，
 #按题目练习数降序。
 */
select
    uid,
    count(distinct case when  year(exam_record.submit_time) = '2021' then exam_record.id else null end) as exam_cnt,
    count(distinct case when  year(practice_record.submit_time) = '2021' then practice_record.id else null end) as question_cnt
from
    (
        select
            uid
        from
            exam_record
            left join examination_info using(exam_id)
            left join user_info using(uid)
        where
            difficulty = 'hard'
            and tag = 'SQL'
            and level = '7'
            and year(exam_record.submit_time) = '2021'
        group by
            uid
        having
            avg(exam_record.score) > 80
    ) mindle
    left join exam_record using (uid)
    left join practice_record using (uid)
group by
    uid
order by
    exam_cnt,
    question_cnt desc;