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

select tb_active_user.dt,count(tb_active_user.uid) dau,round(count(tb_register.uid)/count(tb_active_user.uid),2) uv_new_ratio
from(
    (select uid,date(in_time) dt from tb_user_log
        group by dt,uid
        union
    select uid,date(out_time) dt from tb_user_log
        group by dt,uid)tb_active_user
    left join(
        select uid,min(date(in_time)) dt from tb_user_log
        group by uid
    )tb_register
    on tb_active_user.uid = tb_register.uid and tb_active_user.dt = tb_register.dt
    
)
group by dt
order by dt

构造用户活跃表与构造的用户注册表左连接，然后根据用户活跃表的日期聚合和排序，count(tb_active_user.uid)即为当日活跃人数，count(tb_register.uid.uid)为新增人数