题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

with s1 as (
    select user_id,room_id,in_time,out_time from user_view_tb
    where mid(in_time,1,2) >= 23 and mid(in_time,1,2) <= 24
    or mid(in_time,1,2) < 23 and mid(out_time,1,2) >= 23
)
select r.room_id,room_name,count(distinct user_id) user_count
from room_info_tb r join s1 on r.room_id = s1.room_id
group by r.room_id,room_name
order by user_count desc,r.room_id