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

select
    d.user_id,
    question_pass_rate,
    pass_rate,
    question_per_cnt
from
    (
        select
            user_id,
            count(question_id) / count(distinct question_id) question_per_cnt
        from
            (
                select
                    user_id,
                    question_id,
                    result_info,
                    done_time
                from
                    test.done_questions_record
                group by
                    user_id,
                    question_id,
                    result_info,
                    done_time
            ) a
        group by
            user_id
        having
            sum(result_info) / count(distinct question_id) > 0.6
    ) d
    join (
        select
            user_id,
            sum(result_info) / count(distinct question_id) question_pass_rate,
            sum(result_info) / count(question_id) pass_rate
        from
            (
                select
                    user_id,
                    question_id,
                    result_info
                from
                    (
                        select
                            user_id,
                            question_id,
                            result_info,
                            done_time
                        from
                            test.done_questions_record
                        group by
                            user_id,
                            question_id,
                            result_info,
                            done_time
                    ) b
                group by
                    user_id,
                    question_id,
                    result_info
            ) c
        group by
            user_id
        having
            sum(result_info) / count(distinct question_id) > 0.6
    ) e on d.user_id = e.user_id