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

select u.device_id, u.university, 
    count(q.question_id) as question_cnt,
    sum(q.number) as right_question_cnt
from(
    select device_id, university from user_profile 
    where university = '复旦大学') as u
left join(
        select device_id, question_id ,
         case 
           when result = 'wrong' or result is null then 0
           when result ='right' then 1
         end as number
        from question_practice_detail 
        where month(date) = 8
    ) as q
    on u.device_id = q.device_id
group by u.device_id