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

select
    up.device_id,
    university,
    case when a.question_cnt is null then 0 else a.question_cnt end ,
    case when a.right_question_cnt is null then 0 else a.right_question_cnt end
from user_profile as up
left join
    (
    select
        distinct qpd.device_id,
        count(question_id) as question_cnt,
        sum(if(result='right',1,0)) as right_question_cnt
    from question_practice_detail as qpd
    where month(`date`)='8'
    group by qpd.device_id
    ) as a
on up.device_id=a.device_id
where university='复旦大学'
group by up.device_id,university