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

with
    temp as (
        select
            u.device_id,
            university,
            result,
            date
        from
            user_profile u
            left join question_practice_detail q on u.device_id = q.device_id
        where
            university = '复旦大学'
            and (
                month (date) = 8
                or date is null
            )
    )
select
    device_id,
    university,
    count(result) as question_cnt,
    sum(if (result = "right", 1, 0)) as right_question_cnt
from
    temp
group by
    device_id,
    university