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

select u.device_id,
       u.university,
       count(if(q.question_id is null,0,1)) as      question_cnt,
       sum(
             if(q.result='right',1,0)
        ) as 
right_question_cnt 

from   user_profile as u
left join   question_practice_detail as q 
on     u.device_id=q.device_id
where  u.university='复旦大学' and q.date like 
       '%08%' 
group by  u.device_id