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

select
    device_id,
    university,
    coalesce(temp.question_cnt,0) as question_cnt,
    coalesce(temp.right_question_cnt,0) as right_question_cnt
from user_profile
left join
    (select 
        device_id,
        count(*) as question_cnt,
        sum(case when result="right" then 1 else 0 end) 
        as right_question_cnt   
    from question_practice_detail
    where month(date)=8
    group by device_id
    ) temp
using (device_id)
where university = "复旦大学"