题解 | #统计复旦用户8月练题情况#

题目解析
总题目数：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="复旦大学"