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