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

#每个用户，8月份，联系的总题目，和回答正确题目数
select 
    u.device_id,
    u.university,
    sum(if(q.result is not null,1,0)) question_cnt,
    sum(if(q.result = "right",1,0)) right_question_cnt
from user_profile u left join question_practice_detail q on u.device_id = q.device_id and q.date between "2021-08-01" and "2021-08-31"
where u.university = "复旦大学" 
group by u.device_id;