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

select up.device_id,
    university,
    count(up.device_id) as question_cnt,
    sum(
        case when result='right' then 1
        else 0
        end
    ) as right_question_cnt
from user_profile as up left join question_practice_detail as qpd
on up.device_id = qpd.device_id
and date between '2021-08-01' and '2021-08-31'
where university = "复旦大学"
group by up.device_id
;

要注意and date between '2021-08-01' and '2021-08-31'的位置在where前，这样才能把没做题的用户加到表里