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

with fresher_detail as(
    select 
    uid,
    min(date(in_time)) first_open
    from
        tb_user_log
    group by 1
)
,dau_detail as(
    select uid,
           dau_date
    from
    (
    select
        uid,
        date(in_time) dau_date
    from
        tb_user_log
    union 
    select uid,
           date(out_time) dau_date
    from
        tb_user_log
) k
)
select
    dau_date dt,
    dau,
    round(case when new_users is null then 0 else new_users end/dau,2) uv_new_ratio
from
(
    select
        dau_date,
        count(distinct uid) dau
    from
        dau_detail
    group by 1
) s1
left join
(
    select
        first_open ,
        count(1) new_users
    from fresher_detail
    group by 1

) s2
on s2.first_open = s1.dau_date
order by 1