牛客SQL134——满足条件的用户的试卷完成数和题目练习数

题目描述
https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf

思路：# 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

先去找满足条件的红名大佬id，然后在根据id去统计他们的做题数
需要注意在where的时候and(year(p.submit_time)=2021 or p.submit_time is null)，里面的or要包在and 里面

# 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

#统计他们的2021年试卷总完成数
select r.uid,count(distinct r.exam_id) as exam_cnt,count(distinct p.submit_time ) as question_cnt
from exam_record as r left join practice_record as p on r.uid=p.uid
where r.uid in(
    # 先找到高难度SQL试卷，平均分大于80，level=7
select uid
from user_info
where level=7 and uid in
(select r.uid  from exam_record as r join examination_info as e on r.exam_id=e.exam_id
where e.tag='SQL' and e.difficulty='hard'
group by r.uid
having avg(score)>=80
)
) and year(r.submit_time)=2021 and (year(p.submit_time)=2021 or p.submit_time is null)
group by r.uid
order by exam_cnt,question_cnt desc