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

select up.device_id, up.university, 
sum(case when qpd.result is not null then 1 else 0 end ) as question_cnt,
sum(case when qpd.result='right' then 1 else 0 end ) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
on up.device_id = qpd.device_id
where up.university = '复旦大学'
and qpd.date between '2021-08-01'and'2021-08-31'
group by up.device_id