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

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