SQL(面试实战07)

1. 某乎问答11月份日人均回答量

现有某乎问答创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id表示问题id、char_len表示回答字数）：

answer_date

author_id

issue_id

char_len

2021-11-01

101

E001

150

2021-11-01

101

E002

200

2021-11-01

102

C003

2021-11-01

103

P001

2021-11-01

104

C003

120

2021-11-01

105

P001

125

2021-11-01

102

P002

105

2021-11-02

101

P001

201

2021-11-02

110

C002

200

2021-11-02

110

C001

225

2021-11-02

110

C002

220

2021-11-03

101

C002

180

2021-11-04

109

E003

130

2021-11-04

109

E001

123

2021-11-05

108

C001

160

2021-11-05

108

C002

120

2021-11-05

110

P001

180

2021-11-05

106

P002

2021-11-05

107

E003

请你统计11月份日人均回答量（回答问题数量/答题人数），按回答日期排序，结果保留两位小数，以上例子的输出结果如下：

answer_date

per_num

2021-11-01

1.40

2021-11-02

2.00

2021-11-03

1.00

2021-11-04

2.00

2021-11-05

1.25

示例1

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
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

题解

select 
    answer_date,
    round(count(issue_id)/count(distinct author_id),2) per_num
from answer_tb
group by answer_date
1
2
3
4
5

2. 某乎问答高质量的回答中用户属于各级别的数量

现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别，共1-6六个级别、sex表示创作者性别)：

author_id

author_level

sex

101

102

103

104

105

106

107

108

109

110

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指问题编号、char_len表示回答字数）：

answer_date

author_id

issue_id

char_len

2021-11-01

101

E001

150

2021-11-01

101

E002

200

2021-11-01

102

C003

2021-11-01

103

P001

2021-11-01

104

C003

120

2021-11-01

105

P001

125

2021-11-01

102

P002

105

2021-11-02

101

P001

201

2021-11-02

110

C002

200

2021-11-02

110

C001

225

2021-11-02

110

C002

220

2021-11-03

101

C002

180

2021-11-04

109

E003

130

2021-11-04

109

E001

123

2021-11-05

108

C001

160

2021-11-05

108

C002

120

2021-11-05

110

P001

180

2021-11-05

106

P002

2021-11-05

107

E003

回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列，以上例子的输出结果如下：

level_cut

num

5-6级

3-4级

1-2级

示例1

drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
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

题解

select level_cut,count(*) num
from 
(
		select 
			author_id,author_level,issue_id,
			case 
			when author_level <3 then "1-2级"
			when author_level <5 then "3-4级"
			else "5-6级"
			end
			level_cut
		from 
		(
			select author_id,author_level,issue_id
			from author_tb
			join answer_tb
			using(author_id)
			where char_len>=100
		)t
)t1
group by level_cut
order by num desc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3. 某乎问答单日回答问题数大于等于3个的所有用户

现有某乎问答创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：

answer_date

author_id

issue_id

char_len

2021-11-01

101

E001

150

2021-11-01

101

E002

200

2021-11-01

102

C003

2021-11-01

103

P001

2021-11-01

104

C003

120

2021-11-01

105

P001

125

2021-11-01

102

P002

105

2021-11-02

101

P001

201

2021-11-02

110

C002

200

2021-11-02

110

C001

225

2021-11-02

110

C002

220

2021-11-03

101

C002

180

2021-11-04

109

E003

130

2021-11-04

109

E001

123

2021-11-05

108

C001

160

2021-11-05

108

C002

120

2021-11-05

110

P001

180

2021-11-05

106

P002

2021-11-05

107

E003

请你统计11月份单日回答问题数大于等于3个的所有用户信息（author_date表示回答日期、author_id表示创作者id，answer_cnt表示回答问题个数），以上例子的输出结果如下：

answer_date

author_id

answer_cnt

2021-11-02

110

注：若有多条数据符合条件，按answer_date、author_id升序排序。

示例1

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
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

题解

select answer_date,author_id,count(*) answer_cnt
from answer_tb
group by answer_date,author_id
having answer_cnt>=3
order by  answer_date,author_id
1
2
3
4
5

4. 某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题

现有某乎问答题目信息表issue_tb如下（其中issue_id代表问题编号，issue_type表示问题类型）：

issue_id

issue_type

E001

Education

E002

Education

E003

Education

C001

Career

C002

Career

C003

Career

C004

Career

P001

Psychology

P002

Psychology

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：

answer_date

author_id

issue_id

char_len

2021-11-01

101

E001

150

2021-11-01

101

E002

200

2021-11-01

102

C003

2021-11-01

103

P001

2021-11-01

104

C003

120

2021-11-01

105

P001

125

2021-11-01

102

P002

105

2021-11-02

101

P001

201

2021-11-02

110

C002

200

2021-11-02

110

C001

225

2021-11-02

110

C002

220

2021-11-03

101

C002

180

2021-11-04

109

E003

130

2021-11-04

109

E001

123

2021-11-05

108

C001

160

2021-11-05

108

C002

120

2021-11-05

110

P001

180

2021-11-05

106

P002

2021-11-05

107

E003

请你统计回答过教育类问题的用户里有多少用户回答过职场类问题，以上例子的输出结果如下：

num

示例1

drop table if exists issue_tb;
CREATE TABLE issue_tb(
issue_id char(10) NOT NULL, 
issue_type char(10) NOT NULL);
INSERT INTO issue_tb VALUES('E001' ,'Education');
INSERT INTO issue_tb VALUES('E002' ,'Education');
INSERT INTO issue_tb VALUES('E003' ,'Education');
INSERT INTO issue_tb VALUES('C001', 'Career');
INSERT INTO issue_tb VALUES('C002', 'Career');
INSERT INTO issue_tb VALUES('C003', 'Career');
INSERT INTO issue_tb VALUES('C004', 'Career');
INSERT INTO issue_tb VALUES('P001' ,'Psychology');
INSERT INTO issue_tb VALUES('P002' ,'Psychology');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
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

题解

select count(distinct author_id) num 
from issue_tb t1 
join answer_tb t2 
on t1.issue_id=t2.issue_id 
where issue_type = 'Career'
and author_id in (
                 select author_id
                 from issue_tb a 
                 join answer_tb b 
                 on a.issue_id=b.issue_id 
                 where issue_type = 'Education'
                 )
1
2
3
4
5
6
7
8
9
10
11
12

5. 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别，共1-6六个级别、sex表示创作者性别)：

author_id

author_level

sex

101

102

103

104

105

106

107

108

109

110

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：

answer_date

author_id

issue_id

char_len

2021-11-01

101

E001

150

2021-11-01

101

E002

200

2021-11-01

102

C003

2021-11-01

103

P001

2021-11-01

104

C003

120

2021-11-01

105

P001

125

2021-11-01

102

P002

105

2021-11-02

101

P001

201

2021-11-02

110

C002

200

2021-11-02

110

C001

225

2021-11-02

110

C002

220

2021-11-03

101

C002

180

2021-11-04

109

E003

130

2021-11-04

109

E001

123

2021-11-05

108

C001

160

2021-11-05

108

C002

120

2021-11-05

110

P001

180

2021-11-05

106

P002

2021-11-05

107

E003

请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：

author_id

author_level

days_cnt

101

示例1

drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);
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

输出

101|6|3

题解

方式一：
-- 最大连续回答问题的天数大于等于3天的用户及其等级
select author_id,author_level,max(rk) days_cnt
from 
(
	select 
		author_id,author_level,dense_rank()over(partition by author_id,base_date order by answer_date) rk
	from 
	(
		select 
			author_id,author_level,answer_date,date_sub(answer_date, interval rk day) base_date
		from 
		(
			select 
			author_id,
			author_level,
			answer_date,
			dense_rank()over(partition by author_id order by answer_date) rk
			from author_tb
			join answer_tb
			using(author_id)
		)t_rank
	)t_row_rank
)t
where rk>=3
group by author_id,author_level
order by author_id

方式二：
	select 
			author_id,author_level,count(distinct answer_date)days_count
		from 
		(
			select 
			author_id,
			author_level,
			answer_date,
			dense_rank()over(partition by author_id order by answer_date) rk
			from author_tb
			join answer_tb
			using(author_id)
		)t_rank
		group by author_id,author_level,DATE_ADD(answer_date, interval -rk day) 
having days_count >=3
order by author_id
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

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦

相关阅读:
SpringBoot启动后执行方法的几种方式
 三层架构的学习和实例
 k8s 配置存储之 Configmap & secret
第九章预训练模型与自己模型参数不匹配和模型微调的具体实现
 决策树算法
 【MySQL】表的增删改查（进阶）
Go语言函数基础
 Clickhouse—MergeTree 数据生命周期
 记内网http洪水攻击，导致网页无法访问一事
 山东大学考研机试题——整数序列
原文地址：https://blog.csdn.net/m0_67401499/article/details/126117693