题解 | #SQL类别高难度试卷得分的截断平均值#
每个6/7级用户活跃情况
http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
select
uid
,exam_cnt
,if(question_cnt is null,0,question_cnt)
from
(select
uid
,count(submit_time) exam_cnt
FROM exam_record
where year(submit_time)=2021
group by uid) a
left join
(select
uid
,count(submit_time) question_cnt
FROM practice_record
where year(submit_time)=2021
group by uid) b
using (uid)
where uid in
(select
uid
from user_info
join exam_record
using (uid)
join examination_info
using (exam_id)
where tag='SQL'
and level=7
and difficulty='hard'
group by uid
having avg(score)>=80)
uid
,exam_cnt
,if(question_cnt is null,0,question_cnt)
from
(select
uid
,count(submit_time) exam_cnt
FROM exam_record
where year(submit_time)=2021
group by uid) a
left join
(select
uid
,count(submit_time) question_cnt
FROM practice_record
where year(submit_time)=2021
group by uid) b
using (uid)
where uid in
(select
uid
from user_info
join exam_record
using (uid)
join examination_info
using (exam_id)
where tag='SQL'
and level=7
and difficulty='hard'
group by uid
having avg(score)>=80)
order by exam_cnt,question_cnt desc
非常考验整合的一题，需要慢慢吃透。记住count（null）=0
