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

select t1.device_id,t1.university,
        count(CASE WHEN t2.question_id is not null THEN 1 else null end) question_cnt,
        count(CASE WHEN t2.result='right' THEN 1 else null end) right_question_cnt
from (select device_id,university
    from user_profile
    where university = '复旦大学'
    ) t1
    left join question_practice_detail t2
    on t1.device_id=t2.device_id and month(t2.date)=8
group by device_id