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

select b.device_id,b.university,count(b.question_id) as question_cnt,
sum(case
    when b.result = 'right'then 1
    else 0
    end) as right_question_cnt
from
(
select *
from(
select u.device_id,u.university,qpd.question_id,qpd.date,qpd.result
from user_profile u left join question_practice_detail qpd on u.device_id = qpd.device_id) a
where a.university = '复旦大学' and (month(a.date) = 8 or a.date is NULL)
) b
group by device_id