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

a.device_id,university,
count(case when result is not null then result else null end) as question_cnt,
count(case when result='right' then result when result is null then null end) as right_question_cnt
from
(
select device_id,university
from user_profile 
where university='复旦大学'
)a
left join
(
    select device_id,
    question_id,
    result
    from question_practice_detail
    where SUBSTR(date,1,7)='2021-08'
)b
on a.device_id=b.device_id
group by a.device_id,university