首页 > 试题广场 >

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

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

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

熊阿熊阿熊

感觉要弄懂不同学校不同难度的平均回答情况，这是第一个关键突破点，然后就是三张表如何进行连接，这是第二关键点，一开始就想到连接三个表做成总表，想象成一个大的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

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号

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

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号

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号

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号

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),

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

Mysql

在思考的大白菜很深情

# 先通过device_id构建一个子查询

# select u.university as university,q.question_id as question_id

# from user_profile as u

# inner join question_practice_detail as q

# on u.device_id = q.device_id

select qu.university,qd.difficult_level,round(count(qd.difficult_level) / count(distinct qu.device_id),4) as avg_answer_cnt

from question_detail as qd

inner join (select u.university as university,q.question_id as question_id,q.device_id as device_id

from user_profile as u

inner join question_practice_detail as q

on u.device_id = q.device_id) as qu

on qd.question_id = qu.question_id

group by qu.university,qd.difficult_level

发表于 2025-08-23 21:50:58 回复(0)

Mysql

一个大比兜

#参加了答题的不同学校，不同难度用户的平均答题量
#分组：学校，题目难度
#平均答题数 = 答题量/答题人数
select u.university,qd.difficult_level,
count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from 
    question_practice_detail as qpd
    inner join user_profile as u
    inner join question_detail as qd
    on qpd.device_id = u.device_id
    and qpd.question_id = qd.question_id
group by u.university,qd.difficult_level

稀里糊涂居然写对了，注意多表内联的字段以及分组问题

发表于 2025-08-20 16:44:18 回复(0)

Mysql

最喜欢秋天的美羊羊很迷人

select
    university
    ,difficult_level
    ,avg(du_num) as avg_answer_cnt
from
    (select
        t2.device_id
        ,t3.difficult_level
        ,count(*) as du_num
    from
        question_practice_detail as t2
    inner join
        question_detail as t3
    on t2.question_id=t3.question_id
    group by difficult_level, device_id) as t
inner join
    (select
        t1.device_id
        ,t1.university
    from user_profile as t1)as tt 
    on tt.device_id=t.device_id
group by university, difficult_level
order by university, difficult_level

相比官方答案先合并三个表再计算，这样做的子查询的运行效率会不会更高？求高手指点~

发表于 2025-08-13 01:45:33 回复(0)

Mysql

牛客960428964号

需要三张表连接，根据学校和难度，分组统计问题数量和设备数，且二者相除即可。

select 
    u.university,
    q2.difficult_level,
    round(count(q1.question_id)/count(distinct q1.device_id),4) as avg_answer_cnt
from 
    question_practice_detail q1 
join
    question_detail q2 
    on q1.question_id=q2.question_id 
join 
    user_profile u 
    on q1.device_id=u.device_id 
group by 
    u.university,q2.difficult_level

发表于 2025-08-06 15:20:26 回复(0)