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

with temp as(
    select
        result,
        difficult_level
    from
        user_profile u
        join question_practice_detail q on u.device_id = q.device_id
        join question_detail de on de.question_id= q.question_id 
    where
        university = '浙江大学'
)
select
    distinct
    difficult_level,
    (sum(if (result = "right", 1, 0)) over(partition by difficult_level) )/(count(result) over(partition by difficult_level)) as correct_rate
from temp
order by correct_rate asc