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

#有些复杂


select 
    t2.dt dt
    ,t3.cnt1 dau
    ,ifnull(t2.ratio,0.00) nv_new_ratio
from
(
select 
    t11.dt
    ,round(t000.cnt2/t11.cnt1,2) ratio
from
(select 
    dt 
    ,count(distinct uid) cnt1
from 
(select uid,date(in_time) dt from tb_user_log 
union 
select uid,date(out_time) dt from tb_user_log ) t1
group by dt
) t11 
left join
(
select 
    dt
    ,count(distinct uid) cnt2
from
(
select 
    uid 
    ,dt 
    ,row_number()over(partition by uid order by dt) rk
from 
(
select uid,date(in_time) dt from tb_user_log 
union 
select uid,date(out_time) dt from tb_user_log 
) t0
) t00 
where rk=1 
group by dt 
) t000 
on t11.dt=t000.dt
) t2
left join 
(select 
    dt 
    ,count(distinct uid) cnt1
from 
(select uid,date(in_time) dt from tb_user_log 
union 
select uid,date(out_time) dt from tb_user_log ) t1
group by dt) t3 
on t2.dt=t3.dt
order by 1