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

# 为什么在where子句限制日期（同样的qpd.date between '2021-08-01' and '2021-08-31'）会出现遗漏，求解答
select
    up.device_id,
    up.university,
    count(qpd.question_id) as question_cnt,
    sum(
        case
            when qpd.result = 'right' then 1 else 0 
        end
    ) as right_question_cnt
from user_profile up
left join question_practice_detail qpd
on up.device_id = qpd.device_id 
    and qpd.date between '2021-08-01' and '2021-08-31'
where up.university = '复旦大学'
group by up.device_id, up.university
order by up.device_id asc