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

select
    a.device_id,
    university,
    sum(
        case
            when b.device_id is not null
            and month (b.date) = 8 then 1
            else 0
        end
    ) as question_cnt,
    sum(
        case
            when b.result = 'right'
            and month (b.date) = 8 then 1
            else 0
        end
    ) as right_question_cnt
from
    user_profile as a
left join 
    question_practice_detail as b on a.device_id = b.device_id
where
    a.university = '复旦大学'
group by a.device_id

to review