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

select t1.device_id, t1.university, sum(if(t2.result is null,0,1)) as question_cnt, sum(if(t2.result='right',1,0)) as right_question_cnt
from
(select device_id, university
from user_profile
where university = '复旦大学'
) t1 
left join question_practice_detail t2
on t1.device_id = t2.device_id
where t2.date between '2021-08-01' and '2021-08-31' or t2.date is null
group by t1.device_id