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

/* question_pass_rate ：每个用户不同题目的通过率，同一用户同一题重复提交通过仅计算一次 = 通过的题目总数/总题目数 通过的题目总数：distinct if(result_info = 1, question_id,null) 总题目数：distinct question_id so question_pass_rate: count(distinct if(result_info = 1, question_id, null)) / jeu44count(distinct question_id) as question_pass_rate pass_rate 表示每个用户的提交正确率（只要有提交一次即计算一次） =通过的次数/总答题次数 通过的次数：sum(result_info) 总答题次数：count(result_info) pass_rate = sum(result_info) / count(result_info) question_per_cnt表示平均每道不同的题目被提交的次数（只要有一次提交即计算一次） =总提交次数/总题目数 总提交次数：一共刷了多少次题，对question_id 进行计数 总题目数：对question_id去重计数 so pass_rate = count(qusetion_id) / count(distinct question_id) */

select

user_id

, count(distinct if(result_info = 1, question_id, null)) / count(distinct question_id) as question_pass_rate

,sum(result_info) / count(result_info) as pass_rate

,count(question_id) / count(distinct question_id) as question_per_cnt

from done_questions_record

group by 1

having question_pass_rate > 0.6