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

select user_id,
count(distinct if(result_info = 1, question_id, null))/count(distinct question_id) as question_pass_rate,
sum(if(result_info=1,1,0))/count(question_id) as pass_rate,
count(question_id)/count(distinct question_id) as question_per_cnt
 from done_questions_record group by user_id
 having question_pass_rate>0.6;

1. 通过率=答对了多少题/一共做了多少题。

由于可以重复做题，而同一用户同一题重复提交通过仅计算一次，因此需要去重。则答对了多少题不能用sum(if(result_info=1,1,0), 得用

count(distinct(if(result_info=1, question_id,null)));一共做了多少题=count(distinct question_id)

1. 提交正确率（只要有提交一次即计算一次）= 不去重版本的 答对多少题目/ 提交次数 = sum(if(result_info=1,1,0))/count(question_id)
2. 平均每道不同的题目被提交的次数（只要有一次提交即计算一次）= 提交了多少次（不论对错）/题目个数 = count(question_id)/count(distinct question_id)