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

with a as(
select uid, date(in_time) as dt from tb_user_log
union
select uid, date(out_time) as dt from tb_user_log
),

b as(select dt, count(*) act from a
group by dt
order by dt asc
),

c as(select uid, ft from( select uid, date(in_time) ft, row_number()over(partition by uid order by in_time asc) as rn from tb_user_log ) z
where rn = 1
),

d as (select ft as dt, count(*) as nu from c
group by dt
order by dt asc
)

select b.dt, b.act, round(coalesce(d.nu, 0)/coalesce(b.act, 0), 2) as new_ratio
from b left join d
on b.dt = d.dt
order by b.dt asc