题解 | #浙大不同难度题目的正确率#

WITH table1 AS (
    SELECT
        p.device_id,
        q.question_id,
        CASE
            WHEN p.result = 'right' THEN 1
            ELSE 0
        END AS result,
        q.difficult_level
    FROM
        question_detail q
    RIGHT JOIN
        question_practice_detail p
    ON
        q.question_id = p.question_id
    WHERE
        p.device_id in (SELECT device_id
                        FROM user_profile
                        WHERE university = '浙江大学')
)
SELECT
    difficult_level,
    SUM(result)/COUNT(result) AS corrct_rate
FROM
    table1
GROUP BY
    difficult_level
ORDER BY corrct_rate