首页 > 试题广场 >

统计每个学校各难度的用户平均刷题数

[编程题]统计每个学校各难度的用户平均刷题数
  • 热度指数：562608 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
用户信息表：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 male 28 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了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
8 5432 117 wrong
9 5432 112 wrong
10 2131 113 right
11 5432 113 wrong
12 2315 115 right
13 2315 116 right
14 2315 117 wrong
15 5432 117 wrong
16 5432 112 wrong
17 2131 113 right
18 5432 113 wrong
19 2315 117 wrong
20 5432 117 wrong
21 5432 112 wrong
22 2131 113 right
23 5432 113 wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误
......
最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误
表：question_detail
id question_id difficult_level
1 111 hard
2 112 medium
3 113 easy
4 115 easy
5 116 medium
6 117 easy

第一行表示: 题目id为111的难度为hard
....
最后行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：
university difficult_level avg_answer_cnt
北京大学 hard 1.0000
复旦大学 easy 1.0000
复旦大学 medium 1.0000
山东大学 easy 4.5000
山东大学 medium 3.0000
浙江大学 easy 5.0000
浙江大学 medium 2.0000
解释：
第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=3)/1 ( device_id=5432) =3.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
);
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');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
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');

输出

北京大学|hard|1.0000
复旦大学|easy|1.0000
复旦大学|medium|1.0000
山东大学|easy|4.5000
山东大学|medium|3.0000
浙江大学|easy|5.0000
浙江大学|medium|2.0000
算法知识视频讲解
添加回答
Mysql
张敬昊头像 张敬昊 
select 
a.university,
c.difficult_level,
count(b.question_id)/count(distinct(a.device_id)) AS avg_answer_cnt
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
GROUP BY
a.university,c.difficult_level
ORDER BY
a.university,c.difficult_level

发表于 2026-02-28 14:26:41 回复(0)
Mysql
想要offer的蚊不叮很理性头像 想要offer的蚊不叮很理性
select
    university,
    difficult_level,
    count(q.question_id) / count(distinct q.device_id) avg_answer_cnt
from
    question_practice_detail as q
    inner join user_profile u on u.device_id = q.device_id
    inner join question_detail qd on qd.question_id = q.question_id
group by
    university,
    difficult_level

发表于 2026-02-20 20:43:39 回复(0)
Mysql
不想打工的社畜很热血头像 不想打工的社畜很热血 
SELECT 
    university,
    difficult_level,
    COUNT(device_id)/COUNT(DISTINCT device_id) AS avg_answer_cnt
FROM user_profile up
JOIN question_practice_detail qdp USING(device_id)
JOIN question_detail qd USING(question_id)
GROUP BY university,difficult_level

发表于 2025-12-30 14:13:48 回复(0)
Mysql
熊阿熊阿熊头像 熊阿熊阿熊
感觉要弄懂不同学校不同难度的平均回答情况，这是第一个关键突破点，然后就是三张表如何进行连接，这是第二关键点，一开始就想到连接三个表做成总表，想象成一个大的excel合并后，再进行操作 
select up.university, qd.difficult_level,
count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from user_profile up 
inner join question_practice_detail qpd on up.device_id	=qpd.device_id
inner join question_detail qd on qpd.question_id=qd.question_id
group by up.university,qd.difficult_level

发表于 2025-12-11 21:42:33 回复(0)
Mysql
天天神券福利官头像 天天神券福利官
select d.university,c.difficult_level,count(c.question_id)/count(distinct c.device_id)
from
(select a.device_id,difficult_level,b.question_id
from question_practice_detail a
inner join question_detail b on a.question_id = b.question_id) c join user_profile d on d.device_id = c.device_id
group by d.university, c.difficult_level
发表于 2025-11-28 01:23:45 回复(0)
Mysql
阿漓的猫头像 阿漓的猫
先看题目，其实这里说的用户平均刷题数是需要用：答题数 / 答题数人；有时候可能会出现一个人答 2 3题的情况，所以需要去重，然后在对每个学校、每个难度进行分组
select
    u.university,qd.difficult_level,
    (
        count(q.question_id) / count(distinct q.device_id)
    ) as avg_answer_cnt
from
    user_profile u
    left join question_practice_detail q on q.device_id = u.device_id
    right join question_detail qd on q.question_id=qd.question_id
group by
    u.university,qd.difficult_level
发表于 2025-11-14 15:42:14 回复(0)
Mysql
在冲浪的我很烦恼头像 在冲浪的我很烦恼
with
    a as (
        select
            device_id,
            count(q.question_id) as question_id_num,
            difficult_level
        from
            question_practice_detail q
            left join question_detail d on q.question_id = d.question_id
        group by
            difficult_level,
            device_id
    )
    ##不同难度学生的答题量
select
    university,
    a.difficult_level,
    avg(a.question_id_num) as avg_answer_cnt
from
    user_profile u
    inner join a on u.device_id = a.device_id
group by
    university,
    a.difficult_level
order by
    university

发表于 2025-11-08 17:06:36 回复(0)
Mysql
雨田yutian头像 雨田yutian 
select
    u.university,
    q.difficult_level,
    round(
        count(qpd.question_id) / count(distinct qpd.device_id),
        4
    ) as avg_answer_cnt
from
    user_profile as u
    join question_practice_detail as qpd on u.device_id = qpd.device_id
    join question_detail as q on qpd.question_id = q.question_id
group by
    u.university,
    q.difficult_level

发表于 2025-11-02 16:41:19 回复(0)
Mysql
牛客470195745号头像 牛客470195745号
select u.university,p.difficult_level,round(avg(p.question_id_count),4) avg_answer_cnt from user_profile u
join (
    select p.device_id,d.difficult_level,count(d.question_id) question_id_count from question_practice_detail p
    join question_detail d on p.question_id = d.question_id
    group by p.device_id,d.difficult_level) p
on u.device_id = p.device_id
group by u.university,p.difficult_level
order by u.university asc
--我看大多数都是计算两个count相除，有人能看懂我这个解法吗？虽然看着麻烦点，但是理解后会感到思路很清晰
发表于 2025-11-01 01:05:34 回复(0)
Mysql
刘的蓝图头像 刘的蓝图
-- 题目的文字内容很长，很容易让人看了前面 忘记 后面

-- 结果导向, 先把输出的字段写入到select，并且在后面做注释, 然后看表与表之间的关联情况
-- 结果保留4位
-- 参加了答题
-- device_id  : user_profile表    1-M question_practice_detail表
-- question_id：question_detail表 1-M question_practice_detail表
-- 平均答题量: 总答题 / 参与过答题

select u.university      as university       -- user_profile表
    , qd.difficult_level as difficult_level  -- question_detail表
    , round(count(1) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from
    user_profile as u
    inner join
        question_practice_detail as qpd
        on u.device_id = qpd.device_id
    inner join
        question_detail as qd
        on qpd.question_id = qd.question_id
group by
    u.university, qd.difficult_level
;
发表于 2025-10-28 18:18:59 回复(0)
Mysql
winter_z头像 winter_z
select
university,
difficult_level,
round(count(result)/count(distinct(u.device_id)), 4) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as p
on u.device_id=p.device_id
inner join question_detail as q
on p.question_id = q.question_id
group by university, difficult_level
发表于 2025-10-27 12:17:13 回复(0)
Mysql
牛客855174877号头像 牛客855174877号
select university, qd.difficult_level, count(qd.question_id)/count(distinct qd.device_id) avg_answer_cnt
from (
    select device_id, q.question_id, difficult_level
    from question_practice_detail as q
    left join question_detail as d
    on q.question_id = d.question_id
) as qd
left join user_profile as u
on u.device_id = qd.device_id
group by university,qd.difficult_level
发表于 2025-10-16 17:05:34 回复(0)
Mysql
牛客307825967号头像 牛客307825967号
select university, difficult_level, round(avg(temp.question_id_count), 4) as avg_answer_cnt
from
    (
        select university, difficult_level, count(qpd.question_id) as question_id_count
        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
        group by university, difficult_level, up.device_id
    ) temp
group by university, difficult_level
order by university
发表于 2025-10-15 10:07:20 回复(0)
Mysql
牛客227029234号头像 牛客227029234号
SELECT university,
       difficult_level,
       round(count(QPD.question_id)/
       count(DISTINCT QPD.device_id),4) AS avg_answer_cnt
FROM user_profile AS U ,为你出在哪儿

      question_detail AS QD,
      question_practice_detail AS QPD
WHERE U.devide_id=QPD.devide_id
AND QD.question_id=QPD.question_id
GROUP BY university,difficult_level;
发表于 2025-09-08 23:24:24 回复(0)
Mysql
十指琴魔头像 十指琴魔
select
    a.university,
    c.difficult_level,
    round(
        count(b.device_id) / count(distinct b.device_id),
        4
    ) as avg_answer_cnt
from
    user_profile a
    inner join question_practice_detail b on a.device_id = b.device_id
    inner join question_detail c on b.question_id = c.question_id
group by
    a.university,
    c.difficult_level

发表于 2025-09-05 14:01:12 回复(0)
提交观点

问题信息

SQL
难度：
512条回答 5767浏览

热门推荐

通过挑战的用户

查看代码

相关试题

统计每个学校各难度的用户平均刷题数

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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