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

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

第一次过滤的目的主要是为了减少数据量