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

with t as (
    select a.device_id,university,question_id,date,result from user_profile a
    join question_practice_detail b on a.device_id = b.device_id
)


select device_id,university,question_cnt,right_question_cnt
from 
(
    select device_id,university,
           count(*) as question_cnt,
           sum(if(result="right",1,0)) as right_question_cnt
    from t
    where month(date) = 8 and university = "复旦大学"
    group by device_id
) c
union 
select device_id,university,0 as question_cnt,0 as right_question_cnt
from t
where month(date) <> 8 and university = "复旦大学"
group by device_id,university