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

SELECT 
    up.device_id,
    up.university,
    count(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
    up.university = '复旦大学'
GROUP BY
    up.device_id