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

我无语了，他这计数标准根本没说清楚。但是对2021年这个条件的限制很值得思考，我没太整明白。。。

with temp as
(select uid
from exam_record 
inner join examination_info using(exam_id)
inner join user_info using(uid)
where level = 7 and tag = "SQL" and difficulty = "hard"
group by uid
having avg(score) > 80)

select 
er.uid,
count(distinct er.exam_id, er.submit_time) as exam_cnt,
count(distinct pr.question_id, pr.submit_time) as question_cnt
from
temp 
inner join exam_record as er on temp.uid = er.uid and year(er.submit_time)=2021
left join practice_record as pr on temp.uid = pr.uid and year(pr.submit_time)=2021
group by er.uid
order by exam_cnt, question_cnt desc