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

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

利用聚合函数统计问题数和答对题目数