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

select
    a.device_id,university,ifnull(b.question_cnt,0),ifnull(b.right_question_cnt,0)
from
    user_profile a
    left join (
        select
            device_id,
            count(question_id) question_cnt,
            sum(if (result = "right", 1, 0)) right_question_cnt
        from
            question_practice_detail
        where
            month (date) = 8
        group by
            device_id
    ) b on a.device_id = b.device_id
where
    university = "复旦大学"