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

select
    device_id,
    '复旦大学' as university,
    coalesce(count(question_id), 0),
    sum(if (result = 'right', 1, 0))
from
    (
        select
            u.device_id,
            q.question_id,
            q.result
        from
            user_profile u
            join question_practice_detail q on u.device_id = q.device_id
        where
            u.university = '复旦大学'
            and month (q.date) = '08'
    ) t1
group by
    device_id