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

select
    dt,
    count(*) as dau,
    round(sum(if (dt = new_time, 1, 0))/count(*),2) as uv_new_ratio
from
    (##求每个用户的活跃日期和注册日期
        select
            uid,
            dt,
            min(dt) over (
                partition by
                    uid
            ) as new_time
        from
            (
                select distinct
                    uid,
                    date (in_time) as dt
                from
                    tb_user_log
                union
                select distinct
                    uid,
                    date (out_time) as dt
                from
                    tb_user_log
            ) t1
    ) t2
group by
    dt
order by
    dt;