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

四个条件：

1.高难度SQL试卷平均分大于80

2.7级红名dalao

3.统计2021年 试卷完成数和题目练习数

4.只保留2021年有试卷完成记录的用户

select t.uid,
count(distinct exam_id) as exam_cnt,
count(distinct question_id,t1.submit_time) as question_cnt
from exam_record t
left join 
(select *
from
practice_record
where year(submit_time)=2021) t1   
on t.uid = t1.uid

where t.uid in (select uid from user_info where level = 7)
  and year(t.submit_time)=2021
  and year(t.submit_time) is not NULL
  and t.uid in 
              (select uid
               from exam_record t
               join examination_info t1 on t.exam_id = t1.exam_id
               where year(t.submit_time)=2021
                 and year(t.submit_time) is not NULL
                 and tag='SQL' 
                 and difficulty='hard'
               group by uid
               having AVG(score)>80
              )

group by t.uid
order by exam_cnt asc,question_cnt desc