题解 | #每篇文章同一时刻最大在看人数#

-- 每篇文章，同一时刻，最大在看人数
with t1 as (select out_time from tb_user_log
group by out_time
),
t2 as (select out_time,in_time,uid,artical_id
from tb_user_log
where artical_id != 0
and in_time >= '2021-11-01'
and in_time <= '2021-11-30'
and out_time >= '2021-11-01'
and out_time <= '2021-11-30')

select artical_id,max(uv) max_uv
from (select artical_id,time_list,sum(yes_or_no) uv
from (select t1.out_time time_list,artical_id,t2.uid,
case when t2.in_time <= t1.out_time and t2.out_time >= t1.out_time then 1 when t2.in_time > t1.out_time then 0 when t2.out_time < t1.out_time then 0 else 0 end yes_or_no from t1
join t2) u
group by artical_id,time_list
order by artical_id,time_list) v
group by artical_id
order by max_uv DESC