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

select t1.device_id,t1.university,
count(question_id) question_cnt,
sum(right_question) right_question_cnt
from
    ( select 
        a.device_id  device_id,
        a.university university,
        question_id,
        case when result='right' then 1
             when result='wrong' then 0
          end   right_question
        from user_profile a  left join  question_practice_detail b
        on a.device_id=b.device_id
        where university='复旦大学' 
        and substr(date,1,7)='2021-08'
    ) t1   
group by t1.device_id,t1.university

union 

select device_id,
university,
'0' as question_cnt,
'0' as right_question_cnt
from user_profile
where device_id not in(select device_id
    from question_practice_detail
    where substr(date,1,7)='2021-08')
    and university='复旦大学'