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

select device_id, university, sum(question), sum(right_question)
from (
select a.device_id as device_id, a.university as university,
       case when question_id is not NULL then 1
            when question_id is NULL then 0
            end question, result,
       case when result="right" then 1
            else 0
            end right_question,
        date
from user_profile as a

left join question_practice_detail as b
on a.device_id= b.device_id
where university="复旦大学" ) as c
where month(date)=8 or date is null
group by device_id, university