# 189 在线教育行业

### **SQL185** **牛客直播转换率**

简单 通过率：27.44% 时间限制：1秒 空间限制：256M

#### 描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

| course\_id | course\_name | course\_datetime      |
| ---------- | ------------ | --------------------- |
| 1          | Python       | 2021-12-1 19:00-21:00 |
| 2          | SQL          | 2021-12-2 19:00-21:00 |
| 3          | R            | 2021-12-3 19:00-21:00 |

用户行为表behavior\_tb如下（其中user\_id表示用户编号、if\_vw表示是否浏览、if\_fav表示是否收藏、if\_sign表示是否报名、course\_id代表课程编号）：

| user\_id | if\_vw | if\_fav | if\_sign | course\_id |
| -------- | ------ | ------- | -------- | ---------- |
| 100      | 1      | 1       | 1        | 1          |
| 100      | 1      | 1       | 1        | 2          |
| 100      | 1      | 1       | 1        | 3          |
| 101      | 1      | 1       | 1        | 1          |
| 101      | 1      | 1       | 1        | 2          |
| 101      | 1      | 0       | 0        | 3          |
| 102      | 1      | 1       | 1        | 1          |
| 102      | 1      | 1       | 1        | 2          |
| 102      | 1      | 1       | 1        | 3          |
| 103      | 1      | 1       | 0        | 1          |
| 103      | 1      | 0       | 0        | 2          |
| 103      | 1      | 0       | 0        | 3          |
| 104      | 1      | 1       | 1        | 1          |
| 104      | 1      | 1       | 1        | 2          |
| 104      | 1      | 1       | 0        | 3          |
| 105      | 1      | 0       | 0        | 1          |
| 106      | 1      | 0       | 0        | 1          |
| 107      | 1      | 0       | 0        | 1          |
| 107      | 1      | 1       | 1        | 2          |
| 108      | 1      | 1       | 1        | 3          |

请你统计每个科目的转换率（sign\_rate(%)，转化率=报名人数/浏览人数，结果保留两位小数）。

注：按照course\_id升序排序。

| course\_id | course\_name | sign\_rate(%) |
| ---------- | ------------ | ------------- |
| 1          | Python       | 50.00         |
| 2          | SQL          | 83.33         |
| 3          | R            | 50.00         |

#### 示例1

输入：

```
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL, 
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);

INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);
```

复制

输出：

```
1|Python|50.00
2|SQL|83.33
3|R|50.00
```

#### 解答

```sql
select c.course_id,
       c.course_name,
       round(sum(if_sign) * 100 / sum(if_vw), 2)
from course_tb c
         left join behavior_tb b on c.course_id = b.course_id
group by c.course_id, c.course_name
```

### **SQL186** **牛客直播开始时各直播间在线人数**

中等 通过率：29.68% 时间限制：1秒 空间限制：256M

#### 描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

| course\_id | course\_name | course\_datetime      |
| ---------- | ------------ | --------------------- |
| 1          | Python       | 2021-12-1 19:00-21:00 |
| 2          | SQL          | 2021-12-2 19:00-21:00 |
| 3          | R            | 2021-12-3 19:00-21:00 |

上课情况表attend\_tb如下（其中user\_id表示用户编号、course\_id代表课程编号、in\_datetime表示进入直播间的时间、out\_datetime表示离开直播间的时间）：

| user\_id | course\_id | in\_datetime        | out\_datetime       |
| -------- | ---------- | ------------------- | ------------------- |
| 100      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
| 100      | 1          | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
| 101      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
| 102      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
| 104      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
| 101      | 2          | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
| 102      | 2          | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
| 104      | 2          | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
| 107      | 2          | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
| 100      | 3          | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
| 102      | 3          | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
| 108      | 3          | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |

请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course\_id升序排序）：

| course\_id | course\_name | online\_num |
| ---------- | ------------ | ----------- |
| 1          | Python       | 4           |
| 2          | SQL          | 2           |
| 3          | R            | 1           |

#### 示例1

输入：

```
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
```

复制

输出：

```
1|Python|4
2|SQL|2
3|R|1
```

#### 解答

### 一、题目&解题步骤

\*\*题目：\*\***统计每个科目最大同时在线人数（按course\_id排序）**

又是一题求同时在线题。

**解题思路，分三步走：**

首先，取用户进入直播间，并赋值uv为1；取用户离开直播间，并赋值uv为-1；然后使用窗口函数计算直播间的瞬时用户数；最后，取各个科目直播间的瞬时最大值，并按照course\_id排序。

**1）取用户进入直播间，并赋值uv为1；取用户离开直播间，并赋值uv为-1**

```
SELECT course_id,user_id,in_datetime dt,``1` `AS uv FROM attend_tb``UNION ALL``SELECT course_id,user_id,out_datetime dt,-``1` `AS uv FROM attend_tb;
```

![img](https://uploadfiles.nowcoder.com/images/20220411/235454159_1649671746930/17EEA2FBDE2D0B62949F3D166B00E6F8)

**2）使用窗口函数计算直播间的瞬时用户数**

```
SELECT course_id,course_name,SUM(uv)OVER(PARTITION BY course_id ORDER BY dt,uv DESC) uv_cnt ``FROM (SELECT course_id,user_id,in_datetime dt,``1` `AS uv FROM attend_tb``    ``UNION ALL``    ``SELECT course_id,user_id,out_datetime dt,-``1` `AS uv FROM attend_tb)uv_tb ``JOIN course_tb USING(course_id);
```

![img](https://raw.githubusercontent.com/qkd90/figureBed/main/202407171710556.png)

**3）取各个科目直播间的瞬时最大值并按照course\_id排序。**

![img](https://uploadfiles.nowcoder.com/images/20220411/235454159_1649671883560/989AE4625BC73F938F34859156315B43)

```sql
SELECT course_id,course_name,MAX(uv_cnt)max_num
FROM(
	SELECT course_id,course_name,SUM(uv)OVER(PARTITION BY course_id ORDER BY dt,uv DESC) uv_cnt 
	FROM (SELECT course_id,user_id,in_datetime dt,1 AS uv FROM attend_tb
		UNION ALL
		SELECT course_id,user_id,out_datetime dt,-1 AS uv FROM attend_tb)uv_tb 
	JOIN course_tb USING(course_id)
)t1 GROUP BY course_id,course_name ORDER BY course_id;
```

### **SQL187** **牛客直播各科目平均观看时长**

中等 通过率：38.80% 时间限制：1秒 空间限制：256M

### 描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

| course\_id | course\_name | course\_datetime      |
| ---------- | ------------ | --------------------- |
| 1          | Python       | 2021-12-1 19:00-21:00 |
| 2          | SQL          | 2021-12-2 19:00-21:00 |
| 3          | R            | 2021-12-3 19:00-21:00 |

上课情况表attend\_tb如下（其中user\_id表示用户编号、course\_id代表课程编号、in\_datetime表示进入直播间的时间、out\_datetime表示离开直播间的时间）：

| user\_id | course\_id | in\_datetime        | out\_datetime       |
| -------- | ---------- | ------------------- | ------------------- |
| 100      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
| 100      | 1          | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
| 101      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
| 102      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
| 104      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
| 101      | 2          | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
| 102      | 2          | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
| 104      | 2          | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
| 107      | 2          | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
| 100      | 3          | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
| 102      | 3          | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
| 108      | 3          | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |

请你统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数。

| course\_name | avg\_Len |
| ------------ | -------- |
| SQL          | 91.25    |
| R            | 60.33    |
| Python       | 58.00    |

### 示例1

输入：

```
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
```

复制

输出：

```
SQL|91.25
R|60.33
Python|58.00
```

#### 解答

### **SQL188** **牛客直播各科目出勤率**

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

### 描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

| course\_id | course\_name | course\_datetime      |
| ---------- | ------------ | --------------------- |
| 1          | Python       | 2021-12-1 19:00-21:00 |
| 2          | SQL          | 2021-12-2 19:00-21:00 |
| 3          | R            | 2021-12-3 19:00-21:00 |

用户行为表behavior\_tb如下（其中user\_id表示用户编号、if\_vw表示是否浏览、if\_fav表示是否收藏、if\_sign表示是否报名、course\_id代表课程编号）：

| user\_id | if\_vw | if\_fav | if\_sign | course\_id |
| -------- | ------ | ------- | -------- | ---------- |
| 100      | 1      | 1       | 1        | 1          |
| 100      | 1      | 1       | 1        | 2          |
| 100      | 1      | 1       | 1        | 3          |
| 101      | 1      | 1       | 1        | 1          |
| 101      | 1      | 1       | 1        | 2          |
| 101      | 1      | 0       | 0        | 3          |
| 102      | 1      | 1       | 1        | 1          |
| 102      | 1      | 1       | 1        | 2          |
| 102      | 1      | 1       | 1        | 3          |
| 103      | 1      | 1       | 0        | 1          |
| 103      | 1      | 0       | 0        | 2          |
| 103      | 1      | 0       | 0        | 3          |
| 104      | 1      | 1       | 1        | 1          |
| 104      | 1      | 1       | 1        | 2          |
| 104      | 1      | 1       | 0        | 3          |
| 105      | 1      | 0       | 0        | 1          |
| 106      | 1      | 0       | 0        | 1          |
| 107      | 1      | 0       | 0        | 1          |
| 107      | 1      | 1       | 1        | 2          |
| 108      | 1      | 1       | 1        | 3          |

上课情况表attend\_tb如下（其中user\_id表示用户编号、course\_id代表课程编号、in\_datetime表示进入直播间的时间、out\_datetime表示离开直播间的时间）：

| user\_id | course\_id | in\_datetime        | out\_datetime       |
| -------- | ---------- | ------------------- | ------------------- |
| 100      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
| 100      | 1          | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
| 101      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
| 102      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
| 104      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
| 101      | 2          | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
| 102      | 2          | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
| 104      | 2          | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
| 107      | 2          | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
| 100      | 3          | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
| 102      | 3          | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
| 108      | 3          | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |

请你统计每个科目的出勤率（attend\_rate(%)，结果保留两位小数），出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course\_id升序排序，以上数据的输出结果如下：

| course\_id | course\_name | attend\_rate(%) |
| ---------- | ------------ | --------------- |
| 1          | Python       | 75.00           |
| 2          | SQL          | 60.00           |
| 3          | R            | 66.67           |

### 示例1

输入：

```
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);

INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL, 
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);

INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
```

复制

输出：

```
1|Python|75.00
2|SQL|60.00
3|R|66.67
```

### **SQL189** **牛客直播各科目同时在线人数**

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

### 描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

| course\_id | course\_name | course\_datetime      |
| ---------- | ------------ | --------------------- |
| 1          | Python       | 2021-12-1 19:00-21:00 |
| 2          | SQL          | 2021-12-2 19:00-21:00 |
| 3          | R            | 2021-12-3 19:00-21:00 |

上课情况表attend\_tb如下（其中user\_id表示用户编号、course\_id代表课程编号、in\_datetime表示进入直播间的时间、out\_datetime表示离开直播间的时间）：

| user\_id | course\_id | in\_datetime        | out\_datetime       |
| -------- | ---------- | ------------------- | ------------------- |
| 100      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
| 100      | 1          | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
| 101      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
| 102      | 1          | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
| 104      | 1          | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
| 101      | 2          | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
| 102      | 2          | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
| 104      | 2          | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
| 107      | 2          | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
| 100      | 3          | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
| 102      | 3          | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
| 108      | 3          | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |

请你统计每个科目最大同时在线人数（按course\_id排序），以上数据的输出结果如下：

| course\_id | course\_name | max\_num |
| ---------- | ------------ | -------- |
| 1          | Python       | 4        |
| 2          | SQL          | 4        |
| 3          | R            | 3        |

### 示例1

输入：

```
drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');
```

复制

输出：

```
1|Python|4
2|SQL|4
3|R|3
```


---

# 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/185189-zai-xian-jiao-yu-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.