首页 > 试题广场 >

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

[编程题]浙大不同难度题目的正确率
  • 热度指数：261430 时间限制：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
Qiuxj头像 Qiuxj 
SELECT difficult_level,
AVG(IF(result='right',1,0)) AS correct_rate
FROM user_profile u, question_practice_detail qpd, question_detail qd
WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id
AND university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;

发表于 2021-09-29 22:58:04 回复(15)
正儿八经欸头像 正儿八经欸
没有上一题难...
现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目，然后就写得很顺手呢。如下：
现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况，请取出相应数据，并按照准确率升序(order by asc)输出。 

SELECT qd.difficult_level as difficult_level,
sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate
FROM user_profile u
RIGHT JOIN question_practice_detail qpd
ON (u.device_id = qpd.device_id)
LEFT JOIN question_detail qd
ON (qd.question_id = qpd.question_id)
WHERE u.university = "浙江大学"
GROUP BY qd.difficult_level
ORDER BY correct_rate ASC


发表于 2021-11-25 21:05:25 回复(14)
Mysql
YonChun头像 YonChun 
select difficult_level,
count(if(result='right',1,null)) / count(result) as correct_rate
from user_profile up
join question_practice_detail using(device_id)
join question_detail using(question_id)
where university='浙江大学'
group by difficult_level
order by correct_rate
应该是最精简的了吧
发表于 2022-01-31 11:04:12 回复(12)
Mysql
老狗丶头像 老狗丶 
select difficult_level,
(sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate
from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id
inner join question_detail qd on qd.question_id = qpd.question_id 
where university = "浙江大学"
group by difficult_level
order by correct_rate;

发表于 2021-08-28 16:11:28 回复(15)
智能路障头像 智能路障
这题考察的是多表查询，只要把条件梳理到位就可迎刃而解 
select 
     difficult_level,
count(if(result = 'right', 1, null)) / count(result) as correct_rate
from user_profile t1,
     question_practice_detail t2,
     question_detail t3
where t1.device_id = t2.device_id and t2.question_id = t3.question_id
and university = '浙江大学'
group by difficult_level
order by correct_rate

发表于 2021-10-31 11:17:26 回复(4)
Baily1234头像 Baily1234 
SELECT    
    difficult_level,
    SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate 
FROM
    question_practice_detail q
    LEFT JOIN user_profile u ON q.device_id = u.device_id
    LEFT JOIN question_detail q2 ON q.question_id = q2.question_id
WHERE
    university = "浙江大学"
GROUP BY
    difficult_level
ORDER BY 
    correct_rate;
发表于 2021-10-15 16:22:28 回复(5)
fire-light-guo头像 fire-light-guo 
select 
    d.difficult_level,
    sum(if(result = 'right', 1, 0)) / count(*) as correct_rate
from 
    user_profile u, question_practice_detail qp, question_detail d
where 
    u.university = '浙江大学' 
    and u.device_id = qp.device_id
    and qp.question_id = d.question_id
group by d.difficult_level
order by correct_rate
1. 做个三表关联
2. 按问题难度分组
    3. sum（）嵌套if（）计算出正确的答题数量，再除以答题总数

发表于 2021-10-13 20:45:23 回复(6)
Mysql
橘金气泡头像 橘金气泡
有没有哪位大神帮我看下我的none是哪步错了吗 

SELECT Q.difficult_level
    ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate
FROM 
(
    (
    SELECT device_id
        ,university
    FROM user_profile
    WHERE university = '浙江大学'
    )U

    LEFT JOIN
    (
    SELECT device_id
        ,question_id
        ,result
    FROM question_practice_detail
    )P
    ON P.device_id = U.device_id
    
    LEFT JOIN
    (
    SELECT question_id
        ,difficult_level
    FROM question_detail
    )Q
    ON Q.question_id = P.question_id
)
GROUP BY Q.difficult_level
ORDER BY correct_rate


发表于 2022-04-05 17:35:03 回复(3)
Mysql
其实是牛哥头像 其实是牛哥 
select
c.difficult_level,
count(if(result='right',a.question_id,null)) / count(a.question_id) as correct_rate
from


(select device_id,question_id,result
from question_practice_detail
)a


join

(select device_id
from user_profile
where university = '浙江大学'
)b
on a.device_id = b.device_id


left join
(
select
question_id,difficult_level
from question_detail
)c


on a.question_id = c.question_id
group by difficult_level
order by correct_rate

发表于 2021-08-27 10:57:13 回复(2)
Mysql
喜欢修勾的劳伦斯希望offer多多头像 喜欢修勾的劳伦斯希望offer多多
select difficult_level, (count(if(result='right',1,null)) / count(a.question_id)) as correct_rate
from
question_practice_detail a
join (select device_id from user_profile where university='浙江大学') b on a.device_id=b.device_id
join question_detail c on a.question_id=c.question_id
group by difficult_level
order by correct_rate
发表于 2021-10-03 00:47:26 回复(4)
Mysql
Matthew_m头像 Matthew_m 
SELECT
  difficult_level,
  COUNT(IF(q.result = 'right', 1, NULL)) / COUNT(p.question_id) AS correct_rate
FROM
  user_profile u
  JOIN question_practice_detail q ON u.device_id = q.device_id
  JOIN question_detail p ON q.question_id = p.question_id
WHERE
  u.university = '浙江大学'
GROUP BY
  p.difficult_level
ORDER BY correct_rate asc;
首先要滤清大致思路，可以把基本的框架先写出来，这道题难度不大，主要是考虑计算正确率时的计算公式
发表于 2022-07-23 16:58:17 回复(0)
Mysql
牛客116773290号头像 牛客116773290号
有大佬帮忙看看哪里不对吗
select
  distinct difficult_level,
  count(IF(result='right',1,0))/ count(result)
  from (
  select 
    qu.device_id,
    qu.question_id,
    qu.result,
    us.university,
    de.difficult_level
  from  user_profile  us
  right join question_practice_detail qu  
  on  us.device_id = qu.device_id
  left join question_detail  de
  on qu.question_id = de.question_id) data1
where data1.university = '浙江大学' 
group by data1.difficult_level
发表于 2022-06-27 20:27:57 回复(2)
Mysql
员序程马黑头像 员序程马黑 
#左连右连
select distinct difficult_level,
    (sum(result_num) over (partition by difficult_level))/(count(question_id)over (partition by difficult_level))as correct_rate
from question_detail
left join
    (select device_id,question_id,
            (case when result='right' then 1 else 0 end) as result_num from question_practice_detail)t
using (question_id)
right join
    (select device_id,university from user_profile where university='浙江大学')t1
using (device_id)
where difficult_level is not null
order by correct_rate

发表于 2022-05-05 20:38:25 回复(0)
Mysql
369186344头像 369186344 
select difficult_level,
(count(case when t2.result = 'right' then 1 else null end)/ count(t2.question_id))correct_rate
from (select qp.device_id,question_id,result from 
question_practice_detail qp where qp.device_id in 
(select device_id from user_profile up where university = '浙江大学'))t2
join question_detail qd
on t2.question_id = qd.question_id
group by difficult_level
order by correct_rate;

发表于 2022-04-22 23:01:00 回复(0)
Mysql
牛客228167738号头像 牛客228167738号 
SELECT
    d.difficult_level,
    count(if(q.result="right", 1, null)) / count(*) AS correct_rate
FROM
    user_profile AS u
    INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id
    INNER JOIN question_detail AS d ON q.question_id = d.question_id
WHERE
    u.university = "浙江大学"
GROUP BY
    d.difficult_level
ORDER BY
    correct_rate ASC;
发表于 2022-04-16 23:45:52 回复(0)
XXCC111111头像 XXCC111111
1、学位为浙江大学，为条件；2、不同难度水平的，就表示可能需要根据难度水平来分组，使用group by函数；3、答题正确率用sum(if(qpd.result = 'right', 1, 0))/count(*) 来计算

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

发表于 2022-03-03 13:22:17 回复(0)
Mysql
柒鳴飞头像 柒鳴飞 
select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) correct_rate 
from user_profile up 
join question_practice_detail qpd on up.device_id = qpd.device_id
join question_detail qd on qpd.question_id = qd.question_id
where university = '浙江大学' 
group by difficult_level
order by correct_rate 

发表于 2021-12-14 16:48:23 回复(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
勇敢的灰太狼在学习头像 勇敢的灰太狼在学习 
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)
提交观点

问题信息

难度：
906条回答 3168浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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