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

SELECT
    u.`device_id`,
    u.`university`,
    COUNT(q.`result`) question_cnt,
    SUM(IF (q.`result` = 'right', 1, 0)) right_question_cnt
FROM
    question_practice_detail q
    RIGHT JOIN (
        SELECT
            device_id,
            university
        FROM
            user_profile
        WHERE
            university = '复旦大学'
    ) u ON MONTH (q.`date`) = 8
    AND u.`device_id` = q.`device_id`
GROUP BY
    u.`device_id`;