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

select distinct device_id, university, count(question_id) as question_cnt, sum( case when result = 'right' then 1 else 0 end) as right_question_cnt from ( select device_id, university, question_id, date, result from user_profile left join question_practice_detail using(device_id) where university = '复旦大学' and ( month(date) = '08' or date is null) ) a group by device_id, university