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

select
    dt,
    count(*) as dau,
    round(sum(if (dt = first_in_time, 1, 0)) / count(*), 2) as uv_new_ratio
from
    (
        select
            uid,
            dt,
            min(dt) over (
                partition by
                    uid
            ) as first_in_time
        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 info_tb
    ) as all_info_tb
    group by dt