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

with a as (
select
device_id
from user_profile
where university = '浙江大学'
),
b as (
select
a.device_id,
practice.question_id,
practice.result,
detail.difficult_level
from a
left join question_practice_detail practice
on practice.device_id = a.device_id 
left join question_detail detail 
on detail.question_id = practice.question_id
where detail.difficult_level IN ('hard','medium','easy')
)
select 
difficult_level,
round(sum(if(result = 'right',1,0))/count(*),4) as correct_rate
from b
group by difficult_level
order by correct_rate