我要招人

去企业版

首页 > 试题广场 >

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

[编程题]浙大不同难度题目的正确率

热度指数：412951 时间限制：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

算法知识视频讲解

牛客题解官

发表于 2025-02-17 15:08:16

精华题解题目描述运营团队希望了解浙江大学的用户在不同难度题目下答题的正确率情况。解题思路筛选浙江大学的用户：从 user_profile 表中筛选出 university 为 "浙江大学" 的用户，获取他们的 device_id。关联答题记录和题目难度：使用 q 展开全文

webary

发表于 2021-08-31 20:00:17

题意明确：浙江大学的用户在不同难度题目下答题的正确率情况，按照准确率升序输出问题分解：限定条件：浙江大学的用户；不同难度：difficult_level（question_detail表中的列），需要分组统计，因此用到group by，语法详情；正确率：表面理解就是正确数÷总数，正确的展开全文

金木犀20190306135695

发表于 2021-09-09 09:33:42

浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。输出：题目难度，正确率=正确题数/总题数 qd.difficult_lever sum(if(qpd.result='right',1,0))/count(qpd.device_id) AS correct_rat 展开全文

snowiest

发表于 2021-08-31 21:19:29

SELECT difficult_level,SUM(IF(result='right',1,0))/COUNT(*) AS correct_rateFROM user_profile AS upINNER JOIN question_practice_detail AS qpdINNER JOIN 展开全文

牛客293037194号

发表于 2022-06-09 14:42:32

解析：题目考点为三个表的联结，由于表2（question_practice_detail)同时连接两个表，因此这里使用其为主表，使用left join联结另外两个表，键分别是device_id和question_id，获取到有答题记录的学生名单以及题目的难度；看到大部分人使用的是SUM(IF) 展开全文

bwnsry

发表于 2023-04-10 16:26:23

SELECT qd.difficult_level, (SUM(IF (qpd.result = 'right', 1, 0)) / COUNT(*)) AS correct_rate FROM user_profile up INNER JOIN question_ 展开全文

牛客297995581号

发表于 2022-03-23 10:27:04

select difficult_level, //正确率：正确数/总题数 sum(if(qpd.result = 'right', 1, 0)) / count(qpd.question_id) as correct_rate from question_practice_de 展开全文

第999个小菜鸡

发表于 2022-09-19 14:57:35

##本题需要注意以下几点 1.筛选条件，浙江大学，很多题解单独用了where语句，筛选，其实在连接前两个表时直接在on后筛选即可 2.浙江大学的用户做题的正确率，隐含了条件一定是做过题的，所以使用inner join内连接 3.不同难度正确率的计算，明显要按题目难度分组。正确率=做对的题目数量/做题展开全文

無名小卒

发表于 2021-09-14 11:58:01

SELECT difficult_level, sum(IF(result = 'right',1,0)) / COUNT(result) as correct_rate from user_profile as up right JOIN question_practice_detail as q 展开全文

风雪行

发表于 2022-06-07 22:13:16

浙江大学不同难度题目正确率分析: 浙江大学, 不同难度, 正确率; 题目只需要浙江大学的, 所以可以连表后直接在where中进行过滤, 不同难度正确率,根据难度分组, 该难度答对的题数/该难度总题数即可 if(qpd.result='right', 1, 0)这样其实代码更简洁, 但是个人展开全文

好运的🐮🐮

发表于 2022-08-13 11:09:07

select t3.difficult_level, sum(if (t1.result='right',1,0 ))/count(t1.question_id)correct_rate from question_practice_detail&n 展开全文

问题信息

SQL

难度：

1183条回答 7368收藏 5361浏览

通过挑战的用户

查看代码

IceBear...

2023-03-12 21:32:59
牛客14113...

2023-03-08 03:23:21
牛客84184...

2023-03-03 20:48:22
已注销

2023-02-16 13:10:31
菜菜菜菜鸡鸡鸡鸡

2023-02-07 14:26:17

相关试题

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

扫描二维码，关注牛客网
意见反馈
下载牛客APP，随时随地刷题

扫一扫，把题目装进口袋

求职之前，先上牛客: 扫描二维码，进入QQ群



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

公司地址：北京市朝阳区北苑路北美国际商务中心K1座一层-北京牛客科技有限公司
联系方式：010-60728802 投诉举报电话：010-57596212（朝阳人力社保局）
京ICP备14055008号-4 增值电信业务经营许可证营业执照人力资源服务许可证
京公网安备 11010502036488号

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');

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题