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

# 浙江大学，正确率=正确题目数/总题目数
select difficult_level,
    SUM(CASE WHEN q.result = 'right' THEN 1 ELSE 0 END)/
    count(*) as correct_rate
from user_profile u,question_practice_detail q,question_detail qd
where u.device_id=q.device_id and q.question_id=qd.question_id
    and university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;

#第二种办法
/*
select difficult_level,
    sum(case when result='right' then 1 else 0 end)/
    sum(case when result is not null then 1 else 0 end) 
    as correct_rate
from user_profile u left join question_practice_detail q 
    on u.device_id=q.device_id,question_detail qd
where u.university='浙江大学' and q.question_id=qd.question_id
group by difficult_level
order by correct_rate;*/