题解 | #浙大不同难度题目的正确率#

 -- 限制条件：浙江大学 university = '浙江大学'
-- 分组：题目难度
-- 求正确率
select
    t3.difficult_level,
    avg(if (t2.result = 'right', 1, 0)) as correct_rate
from
    user_profile t1
    join question_practice_detail t2 on t1.device_id = t2.device_id
    join question_detail t3 on t2.question_id = t3.question_id
where
    t1.university = '浙江大学'
group by t3.difficult_level
order by correct_rate;

-- 方式2
select
    t3.difficult_level,
    sum(if(t2.result='right',1,0)) / count(t2.result) as correct_rate
from
    user_profile t1
    join question_practice_detail t2 on t1.device_id = t2.device_id
    join question_detail t3 on t2.question_id = t3.question_id
where
    t1.university = '浙江大学'
group by t3.difficult_level 
order by correct_rate;