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

select zz.device_id ,zz.university,ifnull(hh.question_cnt,0),ifnull(hh.right_question_cnt,0)
from 
(select device_id,university from user_profile u where university='复旦大学') zz left join 
(select up.device_id ,university,count(question_id) question_cnt,
sum(if(result='right',1,0)) right_question_cnt 
from user_profile up join question_practice_detail qpd
on up.device_id = qpd.device_id
where university='复旦大学' and month(date)=8
group by up.device_id,university ) hh
on zz.device_id=hh.device_id