题解 | #验证刷题效果，输出题目真实通过率#

WITH UserQuestionStats AS (
    -- 子查询部分，统计每个用户相关的指标基础数据
    SELECT
        user_id,
        -- 统计通过题目数量（去重，按照题目只计一次通过的逻辑）
        COUNT(DISTINCT CASE WHEN result_info = 1 THEN question_id END) AS passed_questions_distinct,
        -- 统计总题目数量（去重）
        COUNT(DISTINCT question_id) AS total_questions_distinct,
        -- 统计通过题目数量（不去重）
        SUM(CASE WHEN result_info = 1 THEN 1 ELSE 0 END) AS passed_questions_not_distinct,
        -- 统计总提交次数
        COUNT(*) AS total_submissions
    FROM
        done_questions_record
    GROUP BY
        user_id
)
-- 主查询，基于上面统计好的数据计算比率并筛选结果
SELECT
    user_id,
    -- 计算题目通过率
    passed_questions_distinct / total_questions_distinct AS question_pass_rate,
    -- 计算提交正确率
    passed_questions_not_distinct / total_submissions AS pass_rate,
    -- 计算每题目平均提交次数
    total_submissions / total_questions_distinct AS question_per_cnt
FROM
    UserQuestionStats
WHERE
    passed_questions_distinct / total_questions_distinct > 0.6
ORDER BY
    user_id ASC;

理解题意实在太困难了，可见程序员和产品经理之间的Gap有多大~

由于运算逻辑较为复杂，建议使用公共表达式（common table expressions）来提高代码可读性和可维护性