mysql练习--浙大不同难度题目的正确率

描述

 

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

 

示例： user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

示例： question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

 

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

根据示例，你的查询应返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000

select
    difficult_level,
    sum(if(result = 'right', 1, 0)) / count(1) as correct_rate
from (
    select
        tb1.device_id as device_id,
        tb2.question_id as question_id,
        tb2.result as result,
        tb3.difficult_level as difficult_level
    from (
        select 
            distinct device_id
        from user_profile
        where university = '浙江大学'
    ) tb1 
    join (
        select
            device_id,
            question_id,
            result
        from question_practice_detail
    ) tb2 on tb1.device_id = tb2.device_id
    join (
        select
            question_id,
            difficult_level
        from question_detail
    ) tb3 on tb2.question_id = tb3.question_id
) tmp
group by difficult_level
order by correct_rate

思路：创建一个临时表tmp，使用join关联三个表