题解 | #平均活跃天数和月活人数#

#1. 先找出满足SQL,hard, level为7 的用户id，作为筛选userid的条件
#2. 定位满足1 的uid后，计算出完成的考试数，注意需要满足2021年的约束，否则计算出的结果不对
#3. 左连接练习问题表，计算出完成问题，输出
SELECT b.uid,b.exam_cnt,count(p.question_id)as question_cnt
FROM
(
    select r1.uid, count(r1.submit_time)as exam_cnt
    from exam_record r1
    where r1.uid in  
      (select r.uid from exam_record r
       left join examination_info i
       on r.exam_id = i.exam_id  
       left join user_info u
       on r.uid = u.uid and YEAR(r.submit_time) = 2021
       where i.difficulty = 'hard'and i.tag ='SQL' 
                and u.level = 7 
       group by 1
       having avg(r.score) > '80'
       )
    and year(submit_time) = 2021
    group by r1.uid
)b
left join practice_record p 
on b.uid = p.uid and YEAR(p.submit_time) = 2021
group by b.uid
order by 2 , 3 desc