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

【解题思路】

第一步：因为有的用户存在跨天登入登出，将被算在不同日期下的活跃用户，考虑到这一点，将out_time与in_time进行纵向拼接。使用union,这样可以获得不同日期下的活跃用户。

select
    uid
    ,date(in_time) as dt 
from tb_user_log
    union
select
    uid 
    ,date(out_time) as dt 
from tb_user_log

第二步：在每天的活跃用户中，有的是新加入的用户，这部分用户的特点是首次登入时间【最小登入时】和活跃时间相同。为了实现这部分用户的统计，可以直接先统计每个用户的注册时间，也就是最小登入时间，然后与第一步的表做左连接，此时若是活跃时间有连接结果，那么说明用户是新加入的活跃用户。

-- 统计每个用户的注册时间
select 
    uid
    ,date(min(in_time)) as first_dt
from tb_user_log
group by uid

-- 进行左连接
(select
        uid
        ,date(in_time) as dt 
    from tb_user_log
    union
    select
        uid 
        ,date(out_time) as dt 
    from tb_user_log) t2
left join
    (select 
        uid
        ,date(min(in_time)) as first_dt
    from tb_user_log
    group by uid 
    ) t1
on t1.uid = t2.uid and t1.first_dt = t2.dt

第三步：基于以上临时表计算最终结果即可。 完整代码如下：

select
    dt
    ,dau
    ,round(new_user/dau,2) as uv_new_ratio
from    
(select
    t2.dt
    ,count(distinct t2.uid) as dau
    ,count(first_dt) as new_user -- 没有连接结果的为null,自动忽略
from    
    (select
        uid
        ,date(in_time) as dt 
    from tb_user_log
    union
    select
        uid 
        ,date(out_time) as dt 
    from tb_user_log) t2
left join
    (select 
        uid
        ,date(min(in_time)) as first_dt
    from tb_user_log
    group by uid 
    ) t1
on t1.uid = t2.uid and t1.first_dt = t2.dt
group by dt 
) t3
;