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

select dt
        ,count(distinct au.uid) AS dau
        ,round(count(distinct nu.uid)/count(distinct au.uid),2) AS uv_new_ratio
from(
    select uid
        ,date(in_time) AS dt
    from tb_user_log
    union 
    select uid
            ,date(out_time) AS dt
    from tb_user_log
)au
left join
(
    select uid
            ,date(min(in_time)) AS fd
    from tb_user_log
    group by uid
)nu on au.dt = nu.fd and au.uid =  nu.uid
group by dt
order by dt asc

1、日活用户 left join 新户

2、union可以去重，所以uid，in_time union uid，out_time可以把in_time和out_time跨天的uid作为两天的活跃用户给筛选出来。