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

select
    device_id,
    university,
    count(question_id) as question_cnt,
    sum(if(result='right',1,0)) as right_question_cnt
from(
    select
        up.device_id,
        university,
        question_id,
        result,
        date
    from
        user_profile as up
        left join
        question_practice_detail as qpd
        on qpd.device_id=up.device_id
    where
        university='复旦大学'
) as new_table
where
    month(date)=8
    or
    date is null
group by 
    device_id