牛客网非技术快速入门SQL练习题
# 1、查询多列 【入门】
描述
题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | Zhejiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据示例，你的查询应返回以下结果
| device_id | gender | age | university |
|---|---|---|---|
| 2138 | male | 21 | 北京大学 |
| 3214 | male | 复旦大学 | |
| 6543 | female | 20 | 北京大学 |
| 2315 | female | 23 | 浙江大学 |
| 5432 | male | 25 | 山东大学 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138|male|21|北京大学
3214|male|None|复旦大学
6543|female|20|北京大学
2315|female|23|浙江大学
5432|male|25|山东大学
1
2
3
4
5
2
3
4
5
答案
select device_id,gender,age,university
from user_profile
1
2
2
# 2、查询所有列 【入门】
描述
题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据示例，你的查询应返回以下结果:
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | Zhejiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
1|2138|male|21|北京大学|BeiJing
2|3214|male|None|复旦大学|Shanghai
3|6543|female|20|北京大学|BeiJing
4|2315|female|23|浙江大学|ZheJiang
5|5432|male|25|山东大学|Shandong
1
2
3
4
5
2
3
4
5
答案
select *
from user_profile
1
2
2
# 3、查询结果去重 【入门】
描述
题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
示例:user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据示例，你的查询应返回以下结果：
| university |
|---|
| 北京大学 |
| 复旦大学 |
| 浙江大学 |
| 山东大学 |
示例1
输入:
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
北京大学
复旦大学
浙江大学
山东大学
1
2
3
4
2
3
4
答案
select distinct university
from user_profile
1
2
2
# 4、 查询结果限制返回行数 【入门】
描述
题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。
示例：
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据输入，你的查询应返回以下结果：
| device_id |
|---|
| 2138 |
| 3214 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138
3214
1
2
2
答案
select device_id
from user_profile
limit 2
1
2
3
2
3
# 5、将查询后的列重新命名 【入门】
描述
题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据示例，你的查询应返回以下结果：
| user_infos_example |
|---|
| 2138 |
| 3214 |
示例
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138
3214
1
2
2
答案
select device_id as user_infos_example
from user_profile
limit 2
1
2
3
2
3
# 6、查找学校是北大的学生信息 【入门】
描述
题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据示例，你的查询应返回以下结果：
| device_id | university |
|---|---|
| 2138 | 北京大学 |
| 6543 | 北京大学 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138|北京大学
6543|北京大学
1
2
2
答案：
select device_id,university
from user_profile
where university='北京大学'
1
2
3
2
3
# 7、查找年龄大于24岁的用户信息 【入门】
描述
题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。
用户信息表：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据输入，你的 查询应返回以下结果：
| device_id | gender | age | university |
|---|---|---|---|
| 5432 | male | 25 | 山东大学 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
5432|male|25|山东大学
1
答案：
select device_id,gender,age,university
from user_profile
where age>24
1
2
3
2
3
# 8、查找某个年龄段的用户信息【入门】
描述
题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。
用户信息表：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据输入，你的查询应返回以下结果：
| device_id | gender | age |
|---|---|---|
| 2138 | male | 21 |
| 6543 | female | 20 |
| 2315 | female | 23 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138|male|21
6543|female|20
2315|female|23
1
2
3
2
3
答案：
select device_id,gender,age
from user_profile
where age between 20 and 23
1
2
3
2
3
# 9、查找除复旦大学的用户信息 【入门】
题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据输入，你的查询应返回以下结果：
| device_id | gender | age | university |
|---|---|---|---|
| 2138 | male | 21 | 北京大学 |
| 6543 | female | 20 | 北京大学 |
| 2315 | female | 23 | 浙江大学 |
| 5432 | male | 25 | 山东大学 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138|male|21|北京大学
6543|female|20|北京大学
2315|female|23|浙江大学
5432|male|25|山东大学
1
2
3
4
2
3
4
答案：
select device_id,gender,age,university
from user_profile
where university !='复旦大学'
1
2
3
2
3
# 10、用where过滤空值练习 【简单】
题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。
示例：user_profile
| id | device_id | gender | age | university | province |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | Beijing |
| 2 | 3214 | male | 复旦大学 | Shanghai | |
| 3 | 6543 | female | 20 | 北京大学 | Beijing |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
| 5 | 5432 | male | 25 | 山东大学 | Shandong |
根据输入，你的 查询应返回以下结果：
| device_id | gender | age | university |
|---|---|---|---|
| 2138 | male | 21 | 北京大学 |
| 6543 | female | 20 | 北京大学 |
| 2315 | female | 23 | 浙江大学 |
| 5432 | male | 25 | 山东大学 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
输出：
2138|male|21|北京大学
6543|female|20|北京大学
2315|female|23|浙江大学
5432|male|25|山东大学
1
2
3
4
2
3
4
答案：
select device_id,gender,age,university
from user_profile
where age is not null
1
2
3
2
3
# 11、高级操作符练习(1) 【简单】
题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。
示例：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 复旦大学 | 4.0 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
根据输入，你的查询应返回以下结果：
| device_id | gender | age | university | gpa |
|---|---|---|---|---|
| 3214 | male | 复旦大学 | 4.0 | |
| 5432 | male | 25 | 山东大学 | 3.8 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
3214|male|None|复旦大学|4.0
5432|male|25|山东大学|3.8
1
2
2
答案：
select device_id,gender,age,university,gpa
from user_profile
where gender='male' and gpa>3.5
1
2
3
2
3
# 12、高级操作符练习(2）【简单】
题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）
示例：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 复旦大学 | 4.0 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
根据输入，你的查询应返回以下结果：
| device_id | gender | age | university | gpa |
|---|---|---|---|---|
| 2138 | male | 21 | 北京大学 | 3.4 |
| 3214 | male | 复旦大学 | 4.0 | |
| 6543 | female | 20 | 北京大学 | 3.2 |
| 5432 | male | 25 | 山东大学 | 3.8 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
2138|male|21|北京大学|3.4
3214|male|None|复旦大学|4.0
6543|female|20|北京大学|3.2
5432|male|25|山东大学|3.8
1
2
3
4
2
3
4
答案：
select device_id,gender,age,university,gpa
from user_profile
where university='北京大学' or gpa>3.7
1
2
3
2
3
# 13、Where in 和Not in【简单】
题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。
示例：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 复旦大学 | 4.0 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
根据输入，你的查询应返回以下结果：
| device_id | gender | age | university | gpa |
|---|---|---|---|---|
| 2138 | male | 21 | 北京大学 | 3.4 |
| 3214 | male | 复旦大学 | 4.0 | |
| 6543 | female | 20 | 北京大学 | 3.2 |
| 5432 | male | 25 | 山东大学 | 3.8 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
2138|male|21|北京大学|3.4
3214|male|None|复旦大学|4.0
6543|female|20|北京大学|3.2
5432|male|25|山东大学|3.8
1
2
3
4
2
3
4
答案：
select device_id,gender,age,university,gpa
from user_profile
where university in ('北京大学','复旦大学','山东大学')
1
2
3
2
3
# 14、操作符混合运用【中等】
题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据
示例：user_profile
| id | device_id | gender | age | university | province | gpa |
|---|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | BeiJing | 3.4 |
| 2 | 3214 | male | NULL | 复旦大学 | Shanghai | 4 |
| 3 | 6543 | female | 20 | 北京大学 | BeiJing | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | ZheJiang | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | Shandong | 3.8 |
根据输入，你的查询应返回以下结果：(该题对于小数点后面的0不需要计算与统计，后台系统会统一输出小数点后面1位)
| device_id | gender | age | university | gpa |
|---|---|---|---|---|
| 3214 | male | NULL | 复旦大学 | 4 |
| 5432 | male | 25 | 山东大学 | 3.8 |
示例1
输入：
drop table if exists user_profile;
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,
`province` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
3214|male|None|复旦大学|4.0
5432|male|25|山东大学|3.8
1
2
2
答案：
select device_id,gender,age,university,gpa
from user_profile
where (gpa>3.5 and university='山东大学') or (gpa>3.8 and university='复旦大学')
1
2
3
2
3
# 15、 查看学校名称中含北京的用户【简单】
题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。
示例：用户信息表：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 复旦大学 | 4.0 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
根据示例，你的查询应返回如下结果：
| device_id | age | university |
|---|---|---|
| 2138 | 21 | 北京大学 |
| 6543 | 20 | 北京大学 |
| 2131 | 28 | 北京师范大学 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
2138|21|北京大学
6543|20|北京大学
2131|28|北京师范大学
1
2
3
2
3
答案：
select device_id,age,university
from user_profile
where university like '%北京%'
1
2
3
2
3
# 16、查找GPA最高值【简单】
题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据
示例：某user_profile表如下:
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2234 | male | 21 | 北京大学 | 3.2 |
| 2 | 2235 | male | NULL | 复旦大学 | 3.8 |
| 3 | 2236 | female | 20 | 复旦大学 | 3.5 |
| 4 | 2237 | female | 23 | 浙江大学 | 3.3 |
| 5 | 2238 | male | 25 | 复旦大学 | 3.1 |
| 6 | 2239 | male | 25 | 北京大学 | 3.6 |
| 7 | 2240 | male | NULL | 清华大学 | 3.3 |
| 8 | 2241 | female | NULL | 北京大学 | 3.7 |
根据输入，你的查询应返回以下结果，结果保留到小数点后面1位(1位之后的四舍五入):
| gpa |
|---|
| 3.8 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2234,'male',21,'北京大学',3.2);
INSERT INTO user_profile VALUES(2,2235,'male',null,'复旦大学',3.8);
INSERT INTO user_profile VALUES(3,2236,'female',20,'复旦大学',3.5);
INSERT INTO user_profile VALUES(4,2237,'female',23,'浙江大学',3.3);
INSERT INTO user_profile VALUES(5,2238,'male',25,'复旦大学',3.1);
INSERT INTO user_profile VALUES(6,2239,'male',25,'北京大学',3.6);
INSERT INTO user_profile VALUES(7,2240,'male',null,'清华大学',3.3);
INSERT INTO user_profile VALUES(8,2241,'female',null,'北京大学',3.7);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
输出：
3.8
1
答案：
select max(gpa)
from user_profile
where university='复旦大学'
1
2
3
2
3
# 17、计算男生人数以及平均GPA【简单】
题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。
示例：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 复旦大学 | 4.0 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
根据输入，你的查询应返回以下结果，结果保留到小数点后面1位(1位之后的四舍五入)：
| male_num | avg_gpa |
|---|---|
| 4 | 3.6 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
4|3.6
1
答案：
select count(1) male_num,avg(gpa) avg_gpa
from user_profile
where gender='male'
1
2
3
2
3
# 18、分组计算练习题【中等】
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
用户信息表：user_profile
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）
| 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.0 | 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 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
。。。
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52
你的查询返回结果需要对性别和学校分组，示例如下，结果保留1位小数，1位小数之后的四舍五入：
| gender | university | user_num | avg_active_day | avg_question_cnt |
|---|---|---|---|---|
| male | 北京大学 | 1 | 7.0 | 2.0 |
| male | 复旦大学 | 2 | 12.0 | 5.5 |
| female | 北京大学 | 1 | 12.0 | 3.0 |
| female | 浙江大学 | 1 | 5.0 | 1.0 |
| male | 山东大学 | 2 | 17.5 | 11.0 |
解释:
第一行表示：北京大学的男性用户个数为1，平均活跃天数为7天，平均发帖量为2
。。。
最后一行表示：山东大学的男性用户个数为2，平均活跃天数为17.5天，平均发帖量为11
示例1
输入：
drop table if exists user_profile;
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` float,
`question_cnt` float,
`answer_cnt` float
);
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
输出：
male|北京大学|1|7.0|2.0
male|复旦大学|2|12.0|5.5
female|北京大学|1|12.0|3.0
female|浙江大学|1|5.0|1.0
male|山东大学|2|17.5|11.0
1
2
3
4
5
2
3
4
5
答案：
SELECT gender, university, COUNT(1) AS user_num
, AVG(active_days_within_30) AS avg_active_day, AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university, gender
1
2
3
4
2
3
4
# 19、分组过滤练习题【简单】
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
示例：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.0 | 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 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 。。。 最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52
根据示例，你的查询应返回以下结果，请你保留3位小数(系统后台也会自动校正)，3位之后四舍五入：
| university | avg_question_cnt | avg_answer_cnt |
|---|---|---|
| 北京大学 | 2.5000 | 21.000 |
| 浙江大学 | 1.000 | 2.000 |
解释: 平均发贴数低于5的学校或平均回帖数小于20的学校有2个
属于北京大学的用户的平均发帖量为2.500，平均回答数量为21.000
属于浙江大学的用户的平均发帖量为1.000，平均回答数量为2.000
示例1
输入：
drop table if exists user_profile;
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` float,
`answer_cnt` float
);
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
输出：
university|avg_question_cnt|avg_answer_cnt
北京大学|2.500|21.000
浙江大学|1.000|2.000
1
2
3
2
3
答案：
select university,avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20
1
2
3
4
2
3
4
# 20、分组排序练习题【简单】
题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。
示例：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.0 | 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 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 。。。 最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52
根据示例，你的查询应返回以下结果：
| university | avg_question_cnt |
|---|---|
| 浙江大学 | 1.0000 |
| 北京大学 | 2.5000 |
| 复旦大学 | 5.5000 |
| 山东大学 | 11.0000 |
示例1
输入：
drop table if exists user_profile;
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
);
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
输出：
浙江大学|1.0000
北京大学|2.5000
复旦大学|5.5000
山东大学|11.0000
1
2
3
4
2
3
4
答案：
select university,avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt
1
2
3
4
2
3
4
# 21、浙江大学用户题目回答情况【中等】
题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
示例 ：question_practice_detail
| id | device_id | question_id | result |
|---|---|---|---|
| 1 | 2138 | 111 | wrong |
| 2 | 3214 | 112 | wrong |
| 3 | 3214 | 113 | wrong |
| 4 | 6543 | 114 | right |
| 5 | 2315 | 115 | right |
| 6 | 2315 | 116 | right |
| 7 | 2315 | 117 | wrong |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误
....
最后一行表示:id为7的用户的常用信息为使用的设备id为2135，在question_id为117的题目上，回答错误
示例：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.0 | 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 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 。。。 最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52
根据示例，你的查询应返回以下结果，查询结果根据question_id升序排序：
解释:
根据题目的数据只有1个浙江大学的用户，那么把浙江大学这个用户所有答题数据查询出来就行
示例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
);
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,118,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,114,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
输出：
2315|115|right
2315|116|right
2315|117|wrong
1
2
3
2
3
答案：
select a.device_id,a.question_id,a.result
from question_practice_detail a
inner join user_profile b on b.device_id=a.device_id and b.university='浙江大学'
1
2
3
2
3
# 22、 统计每个学校的答过题的用户的平均答题数【中等】
运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
用户信息表 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
示例1
输入：
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);
CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
输出：
北京大学|1.0000
复旦大学|2.0000
山东大学|2.0000
浙江大学|3.0000
1
2
3
4
2
3
4
答案：
select a.university,sum(b.answer_cnt)/count(a.device_id) as avg_answer_cnt
from user_profile a
inner join (select device_id,count(question_id) answer_cnt from question_practice_detail group by device_id) b on b.device_id=a.device_id
group by a.university
order by university
1
2
3
4
5
2
3
4
5
# 23、统计每个学校各难度的用户平均刷题数【中等】
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写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
.....
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
输出：
北京大学|hard|1.0000
复旦大学|easy|1.0000
复旦大学|medium|1.0000
山东大学|easy|4.5000
山东大学|medium|3.0000
浙江大学|easy|5.0000
浙江大学|medium|2.0000
1
2
3
4
5
6
7
2
3
4
5
6
7
答案：
select university,difficult_level,round(count(b.question_id)/count(distinct a.device_id),4) avg_answer_cnt
from user_profile a
inner join question_practice_detail b on b.device_id=a.device_id
inner join question_detail c on c.question_id=b.question_id
group by university,difficult_level
1
2
3
4
5
2
3
4
5
# 24、统计每个用户的平均刷题数【中等】
题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据
用户信息表：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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
输出：
山东大学|easy|4.5000
山东大学|medium|3.0000
1
2
2
答案：
select a.university,c.difficult_level,round(count(b.question_id)/count(distinct a.device_id),4) avg_answer_cnt
from user_profile a
inner join question_practice_detail b on b.device_id=a.device_id
inner join question_detail c on c.question_id=b.question_id
where a.university='山东大学'
group by a.university,c.difficult_level
1
2
3
4
5
6
2
3
4
5
6
# 25、查找山东大学或者性别为男生的信息【中等】
题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的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 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
输出：
5432|male|25|3.8
2131|male|28|3.3
2138|male|21|3.4
3214|male|None|4.0
5432|male|25|3.8
2131|male|28|3.3
4321|male|28|3.6
1
2
3
4
5
6
7
2
3
4
5
6
7
答案：
select device_id,gender,age,gpa
from user_profile
where university='山东大学'
union all
select device_id,gender,age,gpa
from user_profile
where gender='male'
1
2
3
4
5
6
7
2
3
4
5
6
7
# 26、计算25岁以上和以下的用户数量【中等】
题目：现在运营想要将用户划分为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 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
输出：
25岁以下|4
25岁及以上|3
1
2
2
答案：
select (case when age>=25 then '25岁及以上' else '25岁以下' end) as age_cut,count(device_id) as number
from user_profile
group by age_cut
1
2
3
2
3
# 27、查看不同年龄段的用户明细【中等】
题目：现在运营想要将用户划分为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岁及以上 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
输出：
2138|male|20-24岁
3214|male|其他
6543|female|20-24岁
2315|female|20-24岁
5432|male|25岁及以上
2131|male|25岁及以上
4321|male|25岁及以上
1
2
3
4
5
6
7
2
3
4
5
6
7
答案：
select device_id,gender,
(case when age <20 then '20岁以下'
when age between 20 and 24 then '20-24岁'
when age >= 25 then '25岁及以上'
else '其他' end) as age_cut
from user_profile
1
2
3
4
5
6
7
2
3
4
5
6
7
# 28、计算用户8月每天的练题数量【中等】
题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
示例：question_practice_detail
| id | device_id | question_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据示例，你的查询应返回以下结果：
| day | question_cnt |
|---|---|
| 13 | 5 |
| 14 | 2 |
| 15 | 3 |
| 16 | 1 |
| 18 | 1 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
输出：
13|5
14|2
15|3
16|1
18|1
1
2
3
4
5
2
3
4
5
答案：
select cast(right(date,2) as SIGNED) day,count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%'
group by day
1
2
3
4
2
3
4
# 29、计算用户的平均次日留存率【困难】
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
示例：question_practice_detail
| id | device_id | quest_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据示例，你的查询应返回以下结果：
| avg_ret |
|---|
| 0.3000 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
输出：
0.3000
1
答案：
select count(distinct b.device_id,b.date)/count(distinct a.device_id,a.date)
from question_practice_detail a
left join question_practice_detail b on b.device_id=a.device_id and b.date=date_add(a.date, interval 1 day)
1
2
3
2
3
# 30、统计每种性别的人数【中等】
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果
示例：user_submit
| device_id | profile | blog_url |
|---|---|---|
| 2138 | 180cm,75kg,27,male | http:/url/bigboy777 |
| 3214 | 165cm,45kg,26,female | http:/url/kittycc |
| 6543 | 178cm,65kg,25,male | http:/url/tiger |
| 4321 | 171cm,55kg,23,female | http:/url/uhksd |
| 2131 | 168cm,45kg,22,female | http:/urlsydney |
根据示例，你的查询应返回以下结果：
| gender | number |
|---|---|
| male | 2 |
| female | 3 |
示例1
输入：
drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
输出：
male|2
female|3
1
2
2
答案：
select substring_index(profile,',',-1) as gender,count(device_id) as number
from user_submit
group by gender
1
2
3
2
3
# 31、提取博客URL中的用户名【中等】
题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。
示例：user_submit
| device_id | profile | blog_url |
|---|---|---|
| 2138 | 180cm,75kg,27,male | http:/ur/bisdgboy777 |
| 3214 | 165cm,45kg,26,female | http:/url/dkittycc |
| 6543 | 178cm,65kg,25,male | http:/ur/tigaer |
| 4321 | 171 cm,55kg,23,female | http:/url/uhksd |
| 2131 | 168cm,45kg,22,female | http:/url/sydney |
根据示例，你的查询应返回以下结果：
| device_id | user_name |
|---|---|
| 2138 | bisdgboy777 |
| 3214 | dkittycc |
| 6543 | tigaer |
| 4321 | uhsksd |
| 2131 | sydney |
示例1
输入：
drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
输出：
2138|bisdgboy777
3214|dkittycc
6543|tigaer
4321|uhsksd
2131|sysdney
1
2
3
4
5
2
3
4
5
答案：
select device_id,substring_index(blog_url,'/',-1) as user_name
from user_submit
1
2
2
# 32、截取出年龄【中等】
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果
示例：user_submit
| device_id | profile | blog_url |
|---|---|---|
| 2138 | 180cm,75kg,27,male | http:/ur/bigboy777 |
| 3214 | 165cm,45kg,26,female | http:/url/kittycc |
| 6543 | 178cm,65kg,25,male | http:/url/tiger |
| 4321 | 171cm,55kg,23,female | http:/url/uhksd |
| 2131 | 168cm,45kg,22,female | http:/url/sydney |
根据示例，你的查询应返回以下结果：
| age | number |
|---|---|
| 27 | 1 |
| 26 | 1 |
| 25 | 1 |
| 23 | 1 |
| 22 | 1 |
示例1
输入：
drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
输出：
27|1
26|1
25|1
23|1
22|1
1
2
3
4
5
2
3
4
5
答案：
select substring_index(substring_index(profile,',',-2),',',1) age,count(1) number
from user_submit
group by age
1
2
3
2
3
# 33、找出每个学校GPA最低的同学【中等】
题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低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 | female | 26 | 复旦大学 | 3.6 | 9 | 6 | 52 |
根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：
| device_id | university | gpa |
|---|---|---|
| 6543 | 北京大学 | 3.2000 |
| 4321 | 复旦大学 | 3.6000 |
| 2131 | 山东大学 | 3.3000 |
| 2315 | 浙江大学 | 3.6000 |
示例1
输入：
drop table if exists user_profile;
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
);
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
输出：
6543|北京大学|3.200
4321|复旦大学|3.600
2131|山东大学|3.300
2315|浙江大学|3.600
1
2
3
4
2
3
4
答案：
select a.device_id,a.university,a.gpa
from user_profile a
inner join (select university,min(gpa) gpa
from user_profile
group by university) b on b.university=a.university and b.gpa=a.gpa
order by a.university
1
2
3
4
5
6
2
3
4
5
6
# 34、统计复旦用户8月练题情况【困难】
题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.
示例：用户信息表user_profile
| id | device_id | gender | age | university | gpa | active_days_within_30 |
|---|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 |
| 2 | 3214 | male | 复旦大学 | 4.0 | 15 | |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 |
| 6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 |
| 7 | 4321 | female | 26 | 复旦大学 | 3.6 | 9 |
示例：question_practice_detail
| id | device_id | question_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据示例，你的查询应返回以下结果：
| device_id | university | question-cnt | right_question_cnt |
|---|---|---|---|
| 3214 | 复旦大学 | 3 | 0 |
| 4321 | 复旦大学 | 0 | 0 |
示例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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
输出：
3214|复旦大学|3|0
4321|复旦大学|0|0
1
2
2
答案：
SELECT a.device_id, a.university, ifnull(b.question_cnt, 0) AS question_cnt
, ifnull(b.right_question_cnt, 0) AS right_question_cnt
FROM user_profile a
LEFT JOIN (
SELECT device_id, COUNT(question_id) AS question_cnt
, SUM(if(result = 'right', 1, 0)) AS right_question_cnt
FROM question_practice_detail
WHERE date LIKE '2021-08%'
GROUP BY device_id
) b
ON b.device_id = a.device_id
WHERE a.university = '复旦大学'
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 35、浙大不同难度题目的正确率【困难】
题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
示例： 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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
输出：
easy|0.5000
medium|1.0000
1
2
2
答案：
SELECT b.difficult_level, SUM(sum_right_answer) / SUM(sum_answer_cnt) correct_rate
FROM user_profile a
INNER JOIN (
SELECT a.device_id, b.difficult_level, COUNT(a.question_id) AS sum_answer_cnt
, SUM(if(a.result = 'right', 1, 0)) AS sum_right_answer
FROM question_practice_detail a
LEFT JOIN question_detail b ON b.question_id = a.question_id
GROUP BY a.device_id, b.difficult_level
) b
ON b.device_id = a.device_id
WHERE a.university = '浙江大学'
GROUP BY b.difficult_level
order by correct_rate
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 36、查找后排序【简单】
题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。
示例：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 23 | 复旦大学 | 4 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
根据示例，你的查询应返回以下结果：
| device_id | age |
|---|---|
| 6534 | 20 |
| 2138 | 21 |
| 3214 | 23 |
| 2315 | 23 |
| 5432 | 25 |
| 2131 | 28 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
6543|20
2138|21
3214|23
2315|23
5432|25
2131|28
1
2
3
4
5
6
2
3
4
5
6
答案：
select device_id,age
from user_profile
order by age
1
2
3
2
3
# 37、查找后多列排序【简单】
题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。
用户信息表：user_profile
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 23 | 复旦大学 | 4 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
你的查询应返回以下结果：
| device_id | gpa | age |
|---|---|---|
| 6534 | 3.2 | 20 |
| 2131 | 3.3 | 28 |
| 2138 | 3.4 | 21 |
| 2315 | 3.6 | 23 |
| 5432 | 3.8 | 25 |
| 3214 | 4 | 23 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
6543|3.200|20
2131|3.300|28
2138|3.400|21
2315|3.600|23
5432|3.800|25
3214|4.000|23
1
2
3
4
5
6
2
3
4
5
6
答案：
select device_id,gpa,age
from user_profile
order by gpa,age
1
2
3
2
3
# 38、查找后降序排列【简单】
题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。
示例 user_profile：
| id | device_id | gender | age | university | gpa |
|---|---|---|---|---|---|
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male | 23 | 复旦大学 | 4 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
根据示例，你的查询应返回以下结果：
| device_id | gpa | age |
|---|---|---|
| 3214 | 4 | 23 |
| 5432 | 3.8 | 25 |
| 2315 | 3.6 | 23 |
| 2138 | 3.4 | 21 |
| 2131 | 3.3 | 28 |
| 6543 | 3.2 | 20 |
示例1
输入：
drop table if exists user_profile;
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);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
输出：
3214|4.0|23
5432|3.8|25
2315|3.6|23
2138|3.4|21
2131|3.3|28
6543|3.2|20
1
2
3
4
5
6
2
3
4
5
6
答案：
select device_id,gpa,age
from user_profile
order by gpa desc,age desc
1
2
3
2
3
# 39、21年8月份练题总数【简单】
题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果
示例：question_practice_detail
| id | device_id | question_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据的示例，你的查询应返回以下结果：
| did_cnt | question_cnt |
|---|---|
| 3 | 12 |
示例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,
`date` date 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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
输出：
3|12
1
答案：
select count(distinct device_id) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%'
1
2
3
2
3
编辑 (opens new window)
上次更新: 2023/10/13, 17:39:25