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

select
    a.device_id,
    a.university,
    count(qpd.question_id),
    sum(if (qpd.result = 'right', 1, 0))
from
    (
        select
            up.device_id,
            up.university
        from
            user_profile up
        where
            up.university = '复旦大学'
    ) a
    left join question_practice_detail qpd on a.device_id = qpd.device_id
    and month (qpd.date) = 08
group by
    a.device_id