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

select user.device_id,university,count(id) question_cnt,count(if(result='right',1,null)) right_question_cnt
from (select device_id,university
from user_profile
where university='复旦大学') user
left join (select * from question_practice_detail
where month(date)=8) q_date
on user.device_id=q_date.device_id
group by user.device_id;