首页 > 试题广场 >

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

[编程题]浙大不同难度题目的正确率
  • 热度指数：264234 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

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

示例： 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

示例1

输入

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出

easy|0.5000
medium|1.0000
算法知识视频讲解
添加回答
Mysql
牛客297122349号头像 牛客297122349号 
select difficult_level,
       sum(if(result='right',1,0))/count(result) as correct_rate
from user_profile u
left join question_practice_detail p using (device_id)
left join question_detail d using (question_id)
where university = '浙江大学' and difficult_level is not null
group by difficult_level
order by correct_rate

发表于 2024-04-25 20:23:01 回复(0)
Mysql
Tayspirit头像 Tayspirit 
select difficult_level, (sum(final_result)/count(final_result))correct_rate from
(select difficult_level,
case when result='right' then '1'
else '0' end as final_result from
(select a.device_id,university,b.question_id,result,difficult_level from user_profile a
join question_practice_detail b on a.device_id=b.device_id
join question_detail c on b.question_id=c.question_id
where university='浙江大学')d
) e
group by difficult_level order by correct_rate;

请问哪里有问题了啊？我这个输出结果和答案一样，只有小数点不一样，就说答案错误啊TT
编辑于 2024-04-06 16:39:28 回复(0)
Mysql
等一个offer的馨馨头像 等一个offer的馨馨 
select difficult_level,
count(if(result='right',1,null))/count(1) as correct_rate
from (
    select * 
from question_practice_detail
where device_id in 
(
    select device_id
    from user_profile
    where university='浙江大学'
) 
) t1
left join question_detail t2 on t1.question_id = t2.question_id
group by difficult_level
order by correct_rate

编辑于 2024-04-02 14:29:04 回复(0)
Mysql
勇敢的灰太狼在学习头像 勇敢的灰太狼在学习 
select
     qd.difficult_level,
     sum(
         case
             when qpd.result = 'right' then 1
             else 0
         end
     )/count(qpd.question_id)as correct_rate 
from 
     question_practice_detail qpd
inner join
     user_profile up
on
     up.device_id = qpd.device_id
inner join
     question_detail qd
on  
     qd.question_id = qpd.question_id 
group by
     qd.difficult_level
having 
     up.university = '浙江大学'
order by 
     correct_rate 

友友们，为什么这样错了？
编辑于 2024-03-23 21:28:02 回复(0)
Mysql
杰尼龟8头像 杰尼龟8
select difficult_level,
        sum(答对总数)/sum(答题总数) correct_rate

from question_detail c
join(
    select b.question_id,
            sum(if(result = "right",1,0)) 答对总数,
            count(*) 答题总数
    from user_profile a
    left join question_practice_detail b
    using(device_id)
    where a.university = "浙江大学"
    group by b.question_id,b.result
) d
using(question_id)
group by difficult_level
order by correct_rate
发表于 2024-03-23 09:57:12 回复(0)
Mysql
LLLLLUMOS头像 LLLLLUMOS 
with q as
(select 
t1.id,
t1.device_id,
t1.question_id,
t1.result,
t2.difficult_level
from question_practice_detail t1
left join question_detail t2
on t1.question_id=t2.question_id)

select q.difficult_level difficult_level,
(sum(case when
q.result='right' then 1
else 0
end)/count(answer_cnt)) as correct_rate
from q
join user_profile t3
on q.device_id=t3.device_id
where t3.university='浙江大学'
group by difficult_level
order by correct_rate


编辑于 2024-03-21 15:42:04 回复(0)
Mysql
aki123头像 aki123
请问为何用left join不对？
select difficult_level,sum(if(result='right',1,0))/count(question_id) as correct_rate
from user_profile
left join question_practice_detail qpd using (device_id)
left join question_detail qd using (question_id)
where university='浙江大学'
group by difficult_level
order by correct_rate





编辑于 2024-03-08 00:41:14 回复(1)
Mysql
牛客963261701号头像 牛客963261701号
select qd.difficult_level,
sum(if(qpd.result = 'right', '1', '0'))/count(qpd.question_id) as correct_rate
from question_practice_detail qpd
left join user_profile up using (device_id)
left join question_detail qd using (question_id)
where up.university = '浙江大学'
group by difficult_level
order by correct_rate

请问我这个哪里错了呀
编辑于 2024-03-06 00:46:15 回复(0)
Mysql
KQ88头像 KQ88
求问大神以下代码哪错了，应该怎么修改，感恩！
select q.difficult_level,
count(if(qpd.result='right',1,0))/count(qpd.question_id) correct_rate
from user_profile as u

left join
question_practice_detail qpd
on u.device_id=qpd.device_id

left join question_detail q
on qpd.question_id=q.question_id

where u.university='浙江大学'
group by q.difficult_level

order by correct_rate;

发表于 2024-03-03 16:38:18 回复(0)
Mysql
牛客509866938号头像 牛客509866938号 
select
difficult_level,
sum(case when t.result="right" then 1 else 0 end)/count(result) as correct_rate
from
(
    select
        q.question_id,
        result
    from
        question_practice_detail as q,
        (
            select
                device_id
            from
                user_profile
            where
                university = "浙江大学"
        ) as d
    where
        q.device_id = d.device_id
) as t
left join question_detail as qd on qd.question_id = t.question_id
group by difficult_level


发表于 2024-02-20 19:43:55 回复(0)
Mysql
牛客62780291号头像 牛客62780291号 
select
    difficult_level,
    sum(if(result='right',1,0))/count(a2.question_id) as correct_rate
from user_profile as a1
    ,question_practice_detail as a2
    ,question_detail as a3
where 
    a1.device_id=a2.device_id
    and a2.question_id = a3.question_id
    and university= '浙江大学'
group by difficult_level


编辑于 2024-02-06 15:27:38 回复(0)
Mysql
吃过饭去看星星吧头像 吃过饭去看星星吧 
select
    c.difficult_level,
    round(sum(if (a.result = 'right', 1, 0)) / count(1), 4) correct_rate
from
    question_practice_detail a
    left join user_profile b on a.device_id = b.device_id
    left join question_detail c on a.question_id = c.question_id
where
    b.university = '浙江大学'
group by
    c.difficult_level
order by
    correct_rate asc
编辑于 2024-02-04 16:51:04 回复(0)
Mysql
冰糖薄荷头像 冰糖薄荷
大神们求助：这样写哪里错了呀

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

编辑于 2024-02-04 14:55:53 回复(0)
Mysql
讲文明的小龙虾在努力头像 讲文明的小龙虾在努力
select difficult_level,
       avg(if(qpd.result="right",1,0))as corret_rate
from user_profile as up
inner join question_practice_detail as qpd
        on up.device_id=qpd.device_id
inner join question_detail as qd
        on qpd.question_id=qd.question_id
where up.university="浙江大学"
group by qd.difficult_level
order by corret_rate asc;

select difficult_level,
       avg(if(qpd.result="right",1,0))as corret_rate
from user_profile as up
inner join question_practice_detail as qpd
        on up.device_id=qpd.device_id
inner join question_detail as qd
        on qpd.question_id=qd.question_id
where up.university="浙江大学"
group by qd.difficult_level
order by corret_rate asc;
编辑于 2024-01-23 16:34:05 回复(0)
提交观点

问题信息

难度：
596条回答 3203浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号