# 194 内容行业

### **SQL190** **某乎问答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);
```

复制

输出：

```
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
```

#### 解答

```sql
select answer_date,
       ROUND(count(issue_id) / count(distinct author_id), 2) per_num
    from answer_tb
    where date_format(answer_date, "%Y-%m") = '2021-11'
    group by answer_date
    order by answer_date
```

### **SQL191** **某乎问答高质量的回答中用户属于各级别的数量**

### 描述

现有某乎问答创作者信息表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      | 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        |

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

| level\_cut | num |
| ---------- | --- |
| 5-6级       | 12  |
| 3-4级       | 2   |
| 1-2级       | 1   |

### 示例1

输入：

```sql
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);
```

复制

输出：

```
5-6级|12
3-4级|2
1-2级|1
```

#### 解答

1.case...when可以根据某个字段分成多个类型

```sql
select case
           when b.author_level in (1, 2) then '1-2级'
           when b.author_level in (3, 4) then '3-4级'
           when b.author_level in (5, 6) then '5-6级'
           else '' end as level_cut,
       count(issue_id)    num
from answer_tb a
         left join author_tb b on a.author_id = b.author_id
where char_len >= 100
group by level_cut
order by num desc
```

### **SQL192** **某乎问答单日回答问题数大于等于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      | 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月份单日回答问题数大于等于3个的所有用户信息（author\_date表示回答日期、author\_id表示创作者id，answer\_cnt表示回答问题个数），以上例子的输出结果如下：

| answer\_date | author\_id | answer\_cnt |
| ------------ | ---------- | ----------- |
| 2021-11-02   | 110        | 3           |

注：若有多条数据符合条件，按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);
```

复制

输出：

```
2021-11-02|110|3
```

#### 解答

```sql
```

### **SQL193** **某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题**

### 描述

现有某乎问答题目信息表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      | 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        |

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

| num |
| --- |
| 1   |

### 示例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
```

#### 解答

```sql
```

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

较难 通过率：23.70% 时间限制：1秒 空间限制：256M

### 描述

现有某乎问答创作者信息表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      | 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        |

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

| author\_id | author\_level | days\_cnt |
| ---------- | ------------- | --------- |
| 101        | 6             | 3         |

### 示例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);
```

复制

输出：

```
101|6|3
```

#### 解答

```sql
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qiangrens-organization.gitbook.io/qkd90/shu-ju-ku-he-zhong-jian-jian/sql-yu-ju/190194-nei-rong-hang-ye.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.