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

select
    up.device_id,
    up.university,
    count(qpd.question_id) as question_cnt,
    sum(
        case
            when result = 'right' then 1
            else 0
        end
    ) as right_question_cnt
from
    user_profile up
left join
    question_practice_detail qpd
    on up.device_id=qpd.device_id
    and month(qpd.date)=8
where up.university='复旦大学'
GROUP BY
    up.device_id,
    up.university