题解 | #每天的日活数及新用户占比#

-- 临时表————每个用户第一次登陆数据
with t as(
    select distinct first_login_time,count(uid)over(partition by first_login_time) as num
    from
    (select uid,min(date(in_time)) as first_login_time
    from tb_user_log
    group by uid) t
),
-- 临时表————日活
m as (
    select dtime as dt,count(1) as dau
    from
    (select uid,date(in_time) as dtime
    from tb_user_log
    union 
    select uid,date(out_time) as dtime
    from tb_user_log) t
    group by dt
    order by dt
)
-- 左连日活表,防止出现时间缺失
select dt,dau,if(round(num/dau,2) is null,0.00,round(num/dau,2)) as uv_new_ratio
from m left join t on t.first_login_time = m.dt
order by dt