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

select 
    a.device_id,
    university,
    count(question_id) question_cnt,
    count(if(result = 'right',question_id, null))          right_question_cnt
from 
(
    select 
        *
    from user_profile
    where university = '复旦大学'
) a 
inner join 
(
    select 
        *
    from question_practice_detail
    where substring(date, 1, 7) = '2021-08'
) b
on a.device_id = b.device_id 
group by 
    1,
    2;