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

SELECT
    device_id,
    university,
    COUNT(question_id) AS question_cnt,
    SUM(result) AS right_question_cnt
FROM
    (
        SELECT
            device_id,
            university,
            question_id,
            IF(result = "right", 1, 0) AS result,
            date
        FROM
            user_profile up
            LEFT JOIN question_practice_detail qpd USING (device_id)
        WHERE
            up.university = "复旦大学"
            AND (
                date BETWEEN "2021-08-01" AND "2021-08-31"
                OR date IS NULL
            )
    ) fd
GROUP BY
    device_id