题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
#先找出八月份做过题目的人的答题情况
select
a.device_id,university,count(result) as question_cnt,
sum(case
when result='right' then 1
else 0
end) as right_question_cnt
from
user_profile a join question_practice_detail b on a.device_id=b.device_id
where
date between '2021-08-01' and '2021-08-31' and university='复旦大学'
group by a.device_id
#并集
union
#找是复旦但八月没做题的人，答题和正确都设为0
select
device_id,university,0 as question_cnt,0 as right_question_cnt
from
user_profile
where
university='复旦大学' and device_id!=( #从之前查询的表中找出八月份做过题的人的id，然后把他剔除
select
a.device_id
from
user_profile a join question_practice_detail b on a.device_id=b.device_id
where
date between '2021-08-01' and '2021-08-31' and university='复旦大学'
group by a.device_id)