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

select device_id,university,count(question_id),count(if(result='right',question_id,null))
from
(select u.device_id,u.university,qp.question_id,qp.result,qp.date
from user_profile u
left join question_practice_detail qp
on u.device_id = qp.device_id
where u.university = '复旦大学' and (qp.date like '2021-08%' or date is null)
) t
group by device_id,university