每日一道面试真题之SQL-统计直播间最高在线人数

淘天集团数开岗面试SQL

题目：有表包含字段用户id、直播间id、进入直播间时间、离开直播间时间，统计直播间最高在线人数

表room：用户uid：uid，直播间id：room_id，进入直播间时间：entry_time，离开直播间时间：leave_time

select max(users) as max_users

from (

select sum(nums) over(order by entry_time) as users

from (

select uid, room_id, entry_time, 1 as nums from room

union all

select uid, room_id, leave_time, -1 as nums from room

) t1

) t2