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

SELECT
    up.device_id,
    up.university,
    IFNULL(count(qpd.question_id) ,0) AS question_cnt,
    IFNULL(sum(if(qpd.result='right',1,0) ) ,0) AS right_question_cnt
FROM
    user_profile up
LEFT JOIN 
    question_practice_detail qpd
    ON up.device_id = qpd.device_id
    AND month(date)=8
WHERE up.university = '复旦大学'
GROUP BY up.device_id, up.university