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

question_practice_detail表拿来找到用户的信息和答题月份以及答题次数和正确的关联。

user_profile拿来找到university的信息。

这里要注意sum(if(qpd.question_id = 'right', 1, 0))的使用

select 
    up.device_id,
    up.university,
    count(qpd.question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
left join 
    question_practice_detail as qpd
on qpd.device_id = up.device_id and month(qpd.date)=8

where university = '复旦大学'
group by up.device_id;