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

# select u.device_id, u.university,
#     count(q.question_id) as question_cnt,
#     sum(case when q.result = 'right' then 1 else 0 end) as right_question_cnt
# from 
#     user_profile u left join question_practice_detail q
#     on u.device_id = q.device_id and month(q.date) = '08'
# where 
#     u.university = '复旦大学'
# group by
#     u.device_id;


select u.device_id, u.university,
    count(q.question_id) as question_cnt,
    sum(if(q.result = 'right',1,0)) as right_question_cnt
from user_profile u left join question_practice_detail q 
on u.device_id = q.device_id
where u.university = '复旦大学' and (month(date) = '08' or date is null)
group by
    u.device_id;