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

# 统计所有用户2021年的试卷完成次数和题目练习次数
# 只要2021年有试卷完成记录的用户
select 
    t_re.uid
    , count(distinct case when year(comp_time) = 2021 and t_re.tag = "exam" then comp_time end, t_re.paper_id) as exam_cnt
    , count(distinct case when year(comp_time) = 2021 and t_re.tag = "question" then comp_time end, t_re.paper_id) as question_cnt
from(
    select 
        er.uid
        , er.exam_id as paper_id
        , er.submit_time as comp_time
        , "exam" as tag
    from exam_record as er
    union all 
    select 
        pr.uid
        , pr.question_id as paper_id
        , pr.submit_time as comp_time
        , "question" as tag
    from practice_record as pr
    ) as t_re
where t_re.uid in (
                    select
                        er.uid
                    from exam_record as er
                    left join user_info as ui on er.uid = ui.uid
                    left join examination_info as ei on ei.exam_id = er.exam_id
                    where ui.level = 7 and ei.tag = "SQL" and ei.difficulty = "hard"
                    group by er.uid
                    having avg(er.score) > 80
                   )
group by t_re.uid
having count(distinct case when year(comp_time) = 2021 and t_re.tag = "exam" then comp_time end, t_re.paper_id) > 0
order by exam_cnt, question_cnt desc