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

select
    device_id,
    a.university as university,
    count(if(month(a.date)=08,1,null)) as question_cnt,
    count(if(a.result='right' and month(a.date)=08 ,1,null)) as right_question_cnt
from
(select
    u.device_id as device_id,
    u.university as university,
    q.result as result,
    q.date as date
from    
    user_profile as u
left outer join 
    question_practice_detail as q
on
    u.device_id = q.device_id
where
    u.university='复旦大学') a
group by
    device_id