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

这个题应该很简单，不需要说明

select
    c.time,
    count(1) as dau,
    format(sum(case when c.rk = 1 then 1 else 0 end)/count(1),2) as uv_new_ratio
from 
(
    select
        b.uid,
        b.time,
        row_number() over(partition by b.uid order by b.time) as rk
    from 
    (
        select
            a.uid,date_format(a.in_time,'%Y-%m-%d') as time
        from tb_user_log a 
        union
        select
            a.uid,date_format(a.out_time,'%Y-%m-%d') as time
        from tb_user_log a
    ) b 
) c group by c.time

order by c.time