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

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