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

# select device_id 
# from user_profile
# where month(date)=8
# group by device_id



select e3.device_id,e3.university,count(question_id),sum(if(result='right',1,0))
from (
    select e1.device_id,e1.university,e2.question_id,e2.result
    from(select device_id,university from user_profile where university ='复旦大学') e1
    left join  (select * from question_practice_detail where month(question_practice_detail.date)=8 ) as e2
    on e1.device_id=e2.device_id
) e3
group by e3.device_id