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

select
    up.device_id,
    up.university,
    ifnull (a.question_cnt, 0) as question_cnt,
    ifnull (b.question_rightcnt, 0) as right_question_cnt
from
    user_profile up
    left join (
        select
            qpd.device_id,
            count(question_id) as question_cnt
        from
            question_practice_detail qpd
        where
            month (qpd.date) = 8
        group by
            qpd.device_id
    ) a on up.device_id = a.device_id
    left join (
        select
            qpd.device_id,
            qpd.result,
            count(result) as question_rightcnt
        from
            question_practice_detail qpd
        where
            qpd.result = 'right'
        group by
            qpd.device_id,
            qpd.result
    ) b on a.device_id = b.device_id
where
    up.university = '复旦大学';