题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select
device_id,
university,
sum(if(result is not null and month(date)=8,1,0)) as question_cnt,
sum(if(result='right' and month(date)=8,1,0)) as right_question_cnt
from
(select
u.device_id,u.university,qpd.result,qpd.date
from
user_profile u
left join
question_practice_detail qpd
on u.device_id = qpd.device_id
where u.university='复旦大学') as t
group by device_id;
首先，不能把8月没有答题的用户筛选掉，因此要使用左查询，生成的表如下：
其次，按device_id分别统计。其中8月总答题数量：sum(if(result is not null and month(date)=8,1,0))
8月份回答正确的题目数量： sum(if(result='right' and month(date)=8,1,0))
