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

select user_profile.device_id,
       university,
       count(question_practice_detail.device_id) question_cnt,
       sum(if(result = 'right', 1, 0))           right_question_cnt
from user_profile
         left outer join question_practice_detail
                         on user_profile.device_id = question_practice_detail.device_id and date like '2021-08%'
where university = '复旦大学'
group by university, user_profile.device_id;