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

先计算各个日期的日活

with dau as (
    SELECT dt,count(DISTINCT uid) as 'dau_num'
    from (
        select uid,date(in_time) as 'dt'
        from tb_user_log
        UNION
        select uid,date(out_time) as 'dt'
        from tb_user_log
    ) as A
    group by dt
)

再计算每个用户的注册日期，再以此得出每个日期有几个新用户

with nu as (
    SELECT dt,count(*) as 'nu_num'
    from (
        SELECT uid,min(date(in_time)) as 'dt'
        from tb_user_log
        group by uid
    ) as A
    group by dt
)

最后用每个日期（dau）的表左连接新用户数量表(nu),完整代码：

with dau as (
    SELECT dt,count(DISTINCT uid) as 'dau_num'
    from (
        select uid,date(in_time) as 'dt'
        from tb_user_log
        UNION
        select uid,date(out_time) as 'dt'
        from tb_user_log
    ) as A
    group by dt
),nu as (
    SELECT dt,count(*) as 'nu_num'
    from (
        SELECT uid,min(date(in_time)) as 'dt'
        from tb_user_log
        group by uid
    ) as A
    group by dt
)
SELECT dt,dau_num,round(ifnull(nu_num,0)/dau_num,2)
from dau 
left join nu 
using(dt)