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

with t as (select user_id, room_id
from user_view_tb
where (in_time between "23:00:00" and "24:00:00" )
or (out_time between "23:00:00" and "24:00:00" )
),

t2 as (select t.room_id, t.user_id, r.room_name
from t left join room_info_tb r
on t.room_id = r.room_id)

select room_id, room_name, count(distinct user_id) as user_count
from t2 
group by room_id, room_name
order by user_count desc