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

select a.device_id
, a.university
,count(b.question_id) as question_cnt
,count(if(b.result= 'right',b.question_id,null)) as right_qusetion_cnt

from 
(   select *
    from user_profile 
    where university = '复旦大学'
)
a 


left join 
(
    select *
    from question_practice_detail
    where substring(date,1,7)='2021-08'
)b on a.device_id=b.device_id


group by a.device_id, a.university