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

with
t1 as(
    select
        artical_id,time
    from(
        select artical_id,in_time as time from tb_user_log
        union all
        select artical_id,out_time as time from tb_user_log) a
    where artical_id !=0),
t2 as(select artical_id,uid,in_time,out_time from tb_user_log),
t3 as(
    select
        t1.artical_id,
        t1.time,
        count(distinct t2.uid) uv
    from t1 left join t2 
    on t1.artical_id=t2.artical_id and t1.time between t2.in_time and t2.out_time
    group by 1,2)

select artical_id,max(uv) max_uv
from t3 
group by artical_id
order by max_uv desc