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

select qd.difficult_level, (sum(case when a.result='right' then 1 else 0 end))/(count(a.result)) as correct_rate
from
question_detail qd
inner join 
(select* from 
question_practice_detail q
where q.device_id in 
(select device_id from user_profile where university='浙江大学')) a
on qd.question_id=a.question_id
group by qd.difficult_level
order by correct_rate

#先從user_profile表篩選大學為浙江大學的device_id
#再篩選question_practice_detail表中為浙江大學學生device答題數數據成為a表
#再根據question_id inner join question_detail表和a表
#利用sum(case when......)計算答題正確的次數再除以答題結果的次數為正確率
#利用difficult level計算每個難易度的正確率