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

用recursive生成日期递归，为了保证即使跨多天也能显示所有活跃天数

with recursive t as ( #活跃用户表
select uid,date(in_time) as in_dt,date(out_time) as out_dt from tb_user_log
union all 
select uid,in_dt,date_sub(out_dt,interval 1 day) from t where out_dt>in_dt
)

select t.out_dt as dt,
count(distinct t.uid) as dau,
round(count(distinct a.uid)/count(distinct t.uid),2) as uv_new_ratio
from t 
left join ( #新用户表
    select uid,min(date(in_time)) as min_dt
    from tb_user_log
    group by uid
) as a
on t.uid=a.uid and t.out_dt=a.min_dt
group by t.out_dt
order by t.out_dt