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

50

2021-11-01

103

P001

35

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

45

2021-11-05

107

E003

56

请你统计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);

题解

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

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

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

author_id

author_level

sex

101

6

m

102

1

f

103

1

m

104

3

m

105

4

f

106

2

f

107

2

m

108

5

f

109

6

f

110

5

m

创作者回答情况表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

50

2021-11-01

103

P001

35

2021-11-01

104

C003<