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

select
    device_id
    ,university
    ,count(is_month_08) question_cnt
    ,count(is_right) right_question_cnt
from
(
    select
    up.device_id device_id
    ,university
    ,case 
        when month(date) = '08' then 1 else null end is_month_08
    ,case
        when result = 'right' then 1 else null end is_right
    from
    user_profile up
    left join
    question_practice_detail qpd
    on
    up.device_id = qpd.device_id
    where university = '复旦大学'
    ) t
group by device_id