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

SELECT
    question_pass_rate_tab.user_id AS user_id,
    question_pass_rate,
    pass_rate,
    question_per_cnt
FROM
    -- 1.question_pass_rate题目正确率, 做对了几道（重复做对只算一次）/ 做了几道题
    (
        SELECT
            user_id,
            -- COUNT(DISTINCT IF(result_info = 1, question_id, NULL)),
            -- COUNT(DISTINCT question_id),
            COUNT(DISTINCT IF (result_info = 1, question_id, NULL)) / COUNT(DISTINCT question_id) question_pass_rate
        FROM
            done_questions_record
        GROUP BY
            user_id
    ) question_pass_rate_tab,
    -- 2.pass_rate提交正确率，总提交次数/总正确次数
    (
        SELECT
            user_id,
            -- count(*) smit_cnt, 
            -- count(if(result_info=1,result_info,null))rigth_cnt ,
            COUNT(IF (result_info = 1, result_info, NULL)) / COUNT(*) pass_rate
        FROM
            done_questions_record
        GROUP BY
            user_id
    ) pass_rate_tab,
    -- 3.question_per_cnt
    (
        SELECT
            user_id,
            AVG(smit_cnt) AS question_per_cnt
        FROM
            (
                SELECT
                    user_id,
                    COUNT(*) smit_cnt
                FROM
                    done_questions_record
                GROUP BY
                    user_id,
                    question_id
            ) smit_cnt_tab -- 子查询的结果是每个人每道题的提交次数
        GROUP BY
            user_id
    ) question_per_cnt_tab
WHERE
    question_pass_rate_tab.user_id = pass_rate_tab.user_id
    AND question_pass_rate_tab.user_id = question_per_cnt_tab.user_id
    AND pass_rate_tab.user_id = question_per_cnt_tab.user_id
    AND question_pass_rate > 0.6
ORDER BY
    user_id

题解思路：

1.question_pass_rate题目正确率, 做对了几道（重复做对只算一次）/ 做了几道题，一张表

2.pass_rate提交正确率，总提交次数/总正确次数，两张表

3.3.question_per_cnt，子查询的结果是每个人每道题的提交次数，三张表

4.将以上三张表使用普通连接，并筛出question_pass_rate > 60% 的用户，按照user_id排序