SQL面试题练习 —— 最高峰同时直播人数

题目来源：字节、快手

1 题目

有如下数据记录直播平台主播上播及下播时间，根据该数据计算出平台最高峰同时直播人数。

+----------+----------------------+----------------------+
| user_id  |      start_time      |       end_time       |
+----------+----------------------+----------------------+
| 1        | 2024-04-29 01:00:00  | 2024-04-29 02:01:05  |
| 2        | 2024-04-29 01:05:00  | 2024-04-29 02:03:18  |
| 3        | 2024-04-29 02:00:00  | 2024-04-29 04:03:22  |
| 4        | 2024-04-29 03:15:07  | 2024-04-29 04:33:21  |
| 5        | 2024-04-29 03:34:16  | 2024-04-29 06:10:45  |
| 6        | 2024-04-29 05:22:00  | 2024-04-29 07:01:08  |
| 7        | 2024-04-29 06:11:03  | 2024-04-29 09:26:05  |
| 3        | 2024-04-29 08:00:00  | 2024-04-29 12:34:27  |
| 1        | 2024-04-29 11:00:00  | 2024-04-29 16:03:18  |
| 8        | 2024-04-29 15:00:00  | 2024-04-29 17:01:05  |
+----------+----------------------+----------------------+

结果如下：

+-----------------+
| max_online_cnt  |
+-----------------+
| 3               |
+-----------------+

2 建表语句

CREATE TABLE IF NOT EXISTS t_livestream_log (
    user_id INT,         -- 主播ID
    start_time VARCHAR(255), -- 开始时间
    end_time VARCHAR(255)   -- 结束时间
);
insert into t_livestream_log(user_id, start_time, end_time) values 
(1,'2024-04-29 01:00:00','2024-04-29 02:01:05'),
(2,'2024-04-29 01:05:00','2024-04-29 02:03:18'),
(3,'2024-04-29 02:00:00','2024-04-29 04:03:22'),
(4,'2024-04-29 03:15:07','2024-04-29 04:33:21'),
(5,'2024-04-29 03:34:16','2024-04-29 06:10:45'),
(6,'2024-04-29 05:22:00','2024-04-29 07:01:08'),
(7,'2024-04-29 06:11:03','2024-04-29 09:26:05'),
(3,'2024-04-29 08:00:00','2024-04-29 12:34:27'),
(1,'2024-04-29 11:00:00','2024-04-29 16:03:18'),
(8,'2024-04-29 15:00:00','2024-04-29 17:01:05');

3 题解

-- 在使用WITH语句时，需要在其后面跟着一个主查询，
-- 比如SELECT * FROM t来引用WITH子句中定义的临时表t
with t as 
(
-- 开播记录
select
user_id,
start_time as action_time,
1 as change_cnt
from t_livestream_log
union all 
-- 下播记录
select
user_id,
end_time as action_time,
-1 as change_cnt
from t_livestream_log)
-- 求取累计求和中的最大值，即为当天最高峰同时直播人数 查询语句
select 
max(online_cnt) as max_online_cnt
from
(select
user_id,
action_time,
change_cnt,
-- 对操作日志按照操作时间进行累积求和
sum(change_cnt) over(order by action_time) online_cnt
from t)tt