select 
id1,
university,
count(question_id) question_cnt ,
sum(case when result='right' then 1 else 0 end) right_question_cnt
from
(
select device_id id1,university
from user_profile 
where university='复旦大学'
) a 
left outer join
(
select device_id id2,question_id,result 
from question_practice_detail
where date between '2021-08-01' and '2021-08-31'
) b
on a.id1=b.id2
group by id1,university