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

with zb2 as (with zb as (select uid,in_time dt
from tb_user_log
union   /**/ 
select uid,out_time
from tb_user_log)
select uid,date_format(dt,"%Y-%m-%d") dt,row_number() over(partition by uid order by dt) new_uid /*排序,1为新用户首次登录时间*/
from zb)
select dt,count(distinct uid) dau,round(sum(if(new_uid=1,1,0))/count(distinct uid),2) uv_new_ratio /*1为新用户其他为0*/
from zb2
group by dt
order by dt