sql22描述
运营想要了解每个学校答过题的用户平均答题数量情况,请你取出数据。
用户信息表 user_profile,其中device_id指终端编号(认为每个用户有唯一的一个终端),gender指性别,age指年龄,university指用户所在的学校,gpa是该用户平均学分绩点,active_days_within_30是30天内的活跃天数。
device_id | gender | age | university | gpa | active_days_within_30 |
---|---|---|---|---|---|
2138 | male | 21 | 北京大学 | 3.4 | 7 |
3214 | male | NULL | 复旦大学 | 4 | 15 |
6543 | female | 20 | 北京大学 | 3.2 | 12 |
2315 | female | 23 | 浙江大学 | 3.6 | 5 |
5432 | male | 25 | 山东大学 | 3.8 | 20 |
2131 | male | 28 | 山东大学 | 3.3 | 15 |
4321 | male | 28 | 复旦大学 | 3.6 | 9 |
第一行表示:用户的常用信息为使用的设备id为2138,性别为男,年龄21岁,北京大学,gpa为3.4,在过去的30天里面活跃了7天
最后一行表示:用户的常用信息为使用的设备id为4321,性别为男,年龄28岁,复旦大学,gpa为3.6,在过去的30天里面活跃了9天
答题情况明细表 question_practice_detail,其中question_id是题目编号,result是答题结果。
device_id | question_id | result |
---|---|---|
2138 | 111 | wrong |
3214 | 112 | wrong |
3214 | 113 | wrong |
6543 | 111 | right |
2315 | 115 | right |
2315 | 116 | right |
2315 | 117 | wrong |
5432 | 118 | wrong |
5432 | 112 | wrong |
2131 | 114 | right |
5432 | 113 | wrong |
第一行表示用户的常用信息为使用的设备id为2138,在question_id为111的题目上,回答错误
…
最后一行表示用户的常用信息为使用的设备id为5432,在question_id为113的题目上,回答错误
请你写SQL查找每个学校用户的平均答题数目(说明:某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例,你的查询应返回以下结果(结果保留4位小数),注意:结果按照university升序排序!!!
university | avg_answer_cnt |
---|---|
北京大学 | 1.0000 |
复旦大学 | 2.0000 |
山东大学 | 2.0000 |
浙江大学 | 3.0000 |
解释:
第一行:北京大学总共有2个用户,2138和6543,2个用户在question_practice_detail里面答了2题,平均答题数目为2/2=1.0000
…
最后一行:浙江大学总共有1个用户,2315,这个用户在*question_practice_detail里面答了3题,平均答题数目为3/1=3.0000*
SELECT u.university,COUNT(question_id)/COUNT(DISTINCT u.device_id) AS avg_answer_cnt
FROM user_profile u
INNER JOIN `question_practice_detail` q
ON u.device_id = q.device_id
GROUP BY u.university;
sql23描述
题目:运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量,请你写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 | 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为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 | 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 |
---|---|---|
北京大学 | 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=9)/1 ( device_id=5432) =3.0000
…
SELECT
up.university,
qd.difficult_level,
COUNT(qpd.`question_id`) / COUNT(DISTINCT up.device_id)
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
GROUP BY
up.`university`,
qd.`difficult_level`
sql24描述
题目:运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数,请取出相应数据
用户信息表: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
SELECT
up.`university`,
qd.`difficult_level`,
COUNT(qpd.`question_id`) / COUNT(DISTINCT up.`device_id`) AS avg_answer_cnt
FROM
user_profile AS up
LEFT JOIN `question_practice_detail` AS qpd ON up.device_id = qpd.device_id
LEFT JOIN `question_detail` AS qd ON qpd.question_id = qd.question_id
WHERE
up.`university` = '山东大学'
GROUP BY
qd.`difficult_level`
sql25描述
题目:现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据,请取出相应结果,结果不去重。
示例: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 | 26 | 复旦大学 | 3.6 | 9 | 6 | 52 |
根据示例,你的查询应返回以下结果(注意输出的顺序,先输出学校为山东大学再输出性别为男生的信息):
device_id | gender | age | gpa |
---|---|---|---|
5432 | male | 25 | 3.8 |
2131 | male | 28 | 3.3 |
2138 | male | 21 | 3.4 |
3214 | male | None | 4 |
5432 | male | 25 | 3.8 |
2131 | male | 28 | 3.3 |
4321 | male | 28 | 3.6 |
SELECT
up.device_id,
up.gender,
up.age,
up.gpa
FROM
user_profile AS up
WHERE
up.university = '山东大学'
union all
SELECT
up.device_id,
up.gender,
up.age,
up.gpa
FROM
user_profile AS up
where
up.gender = 'male';
sql26描述
题目:现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
本题注意:age为null 也记为 25岁以下
示例: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 | 26 | 复旦大学 | 3.6 | 9 | 6 | 52 |
根据示例,你的查询应返回以下结果:
age_cut | number |
---|---|
25岁以下 | 4 |
25岁及以上 | 3 |
SELECT
CASE
WHEN age < 25
OR age IS NULL THEN '25岁以下'
WHEN age >= 25 THEN '25岁及以上'
END age_cut,
COUNT(*) number
FROM
user_profile
GROUP BY
age_cut
sql27描述
题目:现在运营想要将用户划分为20岁以下,20-24岁,25岁及以上三个年龄段,分别查看不同年龄段用户的明细情况,请取出相应数据。(注:若年龄为空请返回其他。)
示例: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 | 26 | 复旦大学 | 3.6 | 9 | 6 | 52 |
根据示例,你的查询应返回以下结果:
device_id | gender | age_cut |
---|---|---|
2138 | male | 20-24岁 |
3214 | male | 其他 |
6543 | female | 20-24岁 |
2315 | female | 20-24岁 |
5432 | male | 25岁及以上 |
2131 | male | 25岁及以上 |
4321 | male | 25岁及以上 |
select
device_id,
gender,
case
when age < 20 then '20岁以下'
when age >= 20 and age <= 24 then '20-24岁'
when age >= 25 then '25岁及以上'
else '其他'
end age_cut
from user_profile;