首页 > 试题广场 >

统计每个用户的平均刷题数

[编程题]统计每个用户的平均刷题数
  • 热度指数：269097 时间限制：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 NULL 复旦大学 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为432，性别为男，年龄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
6534
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
山东大学 easy 4.5000
山东大学 medium 3.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=9)/1 ( device_id=5432) =3.0000
示例1

输入

drop table if exists `user_profile`;
drop table if  exists `question_practice_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');

输出

山东大学|easy|4.5000
山东大学|medium|3.0000
算法知识视频讲解
添加回答
Mysql
鸡屎藤鸭屎香头像 鸡屎藤鸭屎香 
SELECT
    university,
    difficult_level,
    -- 计算用户平均答题数，并保留4位小数
    ROUND(
        COUNT(qpd.question_id) / COUNT(DISTINCt u.device_id),
        4
    ) avg_answer_cnt
FROM
    user_profile u
    -- 通过device_id连接question_practice_detail表
    JOIN question_practice_detail qpd ON u.device_id = qpd.device_id
    -- 通过question_id连接question_detail表
    JOIN question_detail qd ON qpd.question_id = qd.question_id
    -- 筛选出“山东大学”
WHERE university = "山东大学"
    -- 按题目难度分组
GROUP BY difficult_level

发表于 2024-05-11 11:57:40 回复(0)
Mysql
Raines_Xu头像 Raines_Xu 
select university, difficult_level,
count(q.question_id)/count(DISTINCT(q.device_id)) as avg_answer_cnt
from user_profile as p, question_practice_detail as q, question_detail as r
where p.device_id = q.device_id and q.question_id = r.question_id
and p.university='山东大学'
group by r.difficult_level;

发表于 2024-04-27 18:35:18 回复(0)
Mysql
牛客827112865号头像 牛客827112865号
select u.university, q2.difficult_level, (count(q1.question_id) / count(distinct(q1.device_id))) as avg_answer_cnt
from user_profile u, question_practice_detail q1, question_detail q2
where u.device_id = q1.device_id and q1.question_id = q2.question_id and u.university = '山东大学'
group by difficult_level
编辑于 2024-04-23 13:35:25 回复(0)
Mysql
楠_枫_头像 楠_枫_
SELECT university, difficult_level,
COUNT(result)/COUNT(DISTINCT(question_practice_detail.device_id))  AS avg_answer_cnt
FROM  user_profile, question_practice_detail, question_detail
WHERE user_profile.device_id = question_practice_detail.device_id
AND question_practice_detail.question_id  = question_detail.question_id
AND university = '山东大学'
GROUP BY university, difficult_level
发表于 2024-04-15 19:15:03 回复(0)
Mysql
牛客533325825号头像 牛客533325825号 
select university,difficult_level,round(count(q.question_id)/count(distinct(q.device_id)),4) avg_answer_cnt
from user_profile u
join question_practice_detail q on u.device_id=q.device_id
join question_detail qd on q.question_id=qd.question_id
where university='山东大学'
group by difficult_level
编辑于 2024-04-06 13:11:11 回复(0)
Mysql
矫健的小刺猬在看牛客头像 矫健的小刺猬在看牛客 
1.题目要的是已答题的山东大学用户，
所以用问题回答信息表为基础来合并用户信息表，
这样就只有已答题的用户才会匹配信息
2.以学校为山东大学作为条件来合并表格（显式内连接）
select university,difficult_level,
    count(qpd.question_id)/count(distinct up.device_id)
from
    user_profile up
    right 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

编辑于 2024-03-18 19:10:43 回复(0)
Mysql
牛客670322619号头像 牛客670322619号 
select university,
    difficult_level,
    round(count(qpd.question_id)/count(distinct qpd.device_id),4) avg_answer_cnt
from user_profile up,
   question_practice_detail qpd,
   question_detail qd
where up.device_id = qpd.device_id
 and qpd.question_id = qd.question_id
 and university like '%山东%'
group by difficult_level
求问为什么用like会报错，而只要把like那句换成university='山东大学'就是正确的？明明这两种表达作用是一样的啊。
编辑于 2024-03-05 17:30:54 回复(0)
Mysql
泪水打湿了我的猪脚饭头像 泪水打湿了我的猪脚饭 
select 
up.university,
qd.difficult_level,
(count(qpd.question_id)/count(distinct qpd.device_id)) avg_answer_cnt
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 up.university,qd.difficult_level
having up.university='山东大学'

发表于 2024-03-03 15:42:15 回复(0)
Mysql
伟大的小章鱼在喝茶头像 伟大的小章鱼在喝茶
SELECT
    university,
    difficult_level,
    COUNT(qpd.question_id)/COUNT(DISTINCT up.device_id)
AS
    avg_answer_cnt
FROM
    question_practice_detail qpd #表2
JOIN
    user_profile up  #表1
ON
    qpd.device_id=up.device_id
JOIN
    question_detail qd  #表3
ON
    qpd.question_id=qd.question_id
WHERE
    university='山东大学'
GROUP BY
    difficult_level;
编辑于 2024-02-23 22:02:21 回复(0)
Mysql
在研究求职打法的小黄鸭很理智头像 在研究求职打法的小黄鸭很理智 
select university,difficult_level,
    round(count(1)/count(distinct qpd.device_id),4) as avg_answer_cnt
from question_practice_detail qpd 
join user_profile u 
    on qpd.device_id = u.device_id and university = '山东大学'
join question_detail q 
    on q.question_id = qpd.question_id
group by university,difficult_level

编辑于 2024-02-19 17:38:38 回复(0)
Mysql
牛客580059908号头像 牛客580059908号 
select u.university,qd.difficult_level,count(q.result)/count(distinct(q.device_id))
from question_practice_detail q
left join user_profile u
on q.device_id=u.device_id
left join question_detail qd
on q.question_id=qd.question_id
where u.university='山东大学'
group by 1,2
小结，选出多表中部分值
1、inner join 选出共有项
2、以最小表做目标，left join其他表补充信息
3、from 多个表、逗号隔开，where 添加筛选条件，达成inner join 效果
注意点
where在group前面，先筛选后分组
where在join后面，先并表后筛选
发表于 2024-02-08 09:46:59 回复(0)
Mysql
小谷围做题家头像 小谷围做题家 
select a.university,c.difficult_level,
round(count(b.question_id)/count(distinct b.device_id),4) 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 university,c.difficult_level
having a.university='山东大学';

发表于 2024-02-05 01:21:55 回复(0)
Mysql
牛客276398119号头像 牛客276398119号
select
    t1.university
    ,t3.difficult_level
    ,count(t2.question_id)/count(distinct t1.device_id) as avg_ans_num

from
    user_profile as t1
    ,question_practice_detail as t2
    ,question_detail as t3
where
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
group by
    t3.difficult_level
编辑于 2024-01-23 22:39:36 回复(0)
Mysql
18370636086头像 18370636086
select university, difficult_level, round(count(u.answer_cnt)/count(distinct u.device_id),4) as avg_answer_cnt

from user_profile as u
left join question_practice_detail as qpd
on u.device_id=qpd.device_id

left join question_detail as qd
on qpd.question_id=qd.question_id

where university='山东大学'

group by difficult_level
编辑于 2023-12-30 17:38:07 回复(0)
Mysql
牛客842213881号头像 牛客842213881号 
SELECT
    B.university,
    C.difficult_level,
    COUNT(A.question_id)/COUNT(DISTINCT A.device_id) AS avg_answer_cnt
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



编辑于 2023-12-16 16:27:11 回复(0)
Mysql
牛客209995905号头像 牛客209995905号
select university,difficult_level,count(q.question_id)/count(distinct q.device_id) as avg_answer_cnt
from user_profile p,question_practice_detail q,question_detail qd
where p.device_id =q.device_id and q.question_id=qd.question_id
and university='山东大学'
group by difficult_level
发表于 2023-11-22 16:28:21 回复(0)
Mysql
有礼貌的垂耳兔在加班头像 有礼貌的垂耳兔在加班 
SELECT u.university, qd.difficult_level, (COUNT(q.question_id)/COUNT(DISTINCT u.device_id)) AS "'用户平均答题数量'"
FROM question_practice_detail q
LEFT JOIN user_profile u
ON q.device_id = u.device_id
LEFT JOIN question_detail qd
on q.question_id = qd.question_id
GROUP BY u.university,qd.difficult_level
HAVING u.university = '山东大学';


发表于 2023-11-14 16:01:52 回复(0)
Mysql
三月江东头像 三月江东 
SELECT
    university,
    difficult_level,
    ROUND(
        COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id),
        4
    ) AS avg_answer_cnt
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
    university = '山东大学'
GROUP BY
    difficult_level

发表于 2023-10-27 11:57:28 回复(0)
Mysql
牛客862668903号头像 牛客862668903号
select u.university,qd.difficult_level,
count(qpd.question_id)/count(distinct(qpd.device_id)) avg_answer_cnt
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
group by difficult_level
having university='山东大学'
我想问问 为什么我这样写回报错哇

发表于 2023-10-27 11:46:01 回复(0)
提交观点

问题信息

难度：
419条回答 2326浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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