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

select
    c1.device_id,
    c1.university,
    sum(qt),
    sum(rt)
from
    (
        select
            *,
            case
                when month (date) = 8 then 1
                else 0
            end as qt,
            if (result = 'right', 1, 0) rt
        from
            (
                select
                    a1.device_id,
                    university,
                    question_id,
                    result,
                    date
                from
                    user_profile a1
                    left join question_practice_detail b1 on a1.device_id = b1.device_id
                where
                    university = '复旦大学'
            ) uni
    ) c1
group by
    device_id,
    university;