题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
题目解析
总题目数：count(question_id)
回答正确的题目数：result=right的数量，sum(if(result="right",1,0))
限定条件：复旦大学，8月，每个用户（group by device_id）
代码：
select s1.device_id,s1.university,s2.question_cnt,s2.right_question_cnt
from
(select count(question_id) question_cnt,device_id,sum(if(result="right",1,0)) right_question_cnt
from question_practice_detail
where month(date)=08
group by device_id)
s2
left join
user_profile
s1
on s1.device_id=s2.device_id
where s1.university="复旦大学"
