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

#所有question_id的difficult_level
#找出university='浙江大学'的device_id对应question_id、difficult_level、result
#按照difficult_level分组，统计不同result

with tmp as
(
select a.question_id, a.result, b.difficult_level
from
( 
select question_id, device_id, result
from question_practice_detail
where device_id in
(
select device_id
from user_profile
where university = '浙江大学'
)
) a
left join question_detail b
on a.question_id = b.question_id
)

select difficult_level, sum(if(result = 'right',1,0)) / count(*) as rate
from tmp
group by difficult_level
order by rate asc