# 指标：新用户占比=当天的新用户数÷当天活跃用户数（日活数）
# 方法1:临时表+连接
# 1.日活数
# SELECT dt,count(uid) dau
# from (
#   select uid,date(in_time) dt
#   from tb_user_log
#   union 
#   select uid,date(in_time)
#   from tb_user_log
# ) t
# group by dt
# 2.当天新用户数
# select min_dt,count(uid)
# from (
#   SELECT uid,min(dt) min_dt
#   from (
#     select uid,date(in_time) dt
#     from tb_user_log
#     union 
#     select uid,date(in_time)
#     from tb_user_log
#   ) t
#   group by uid
# ) t1
# group by min_dt
#3.整合
# with dau_t as (
#   SELECT dt,count(uid) dau
#   from (
#     select uid,date(in_time) dt
#     from tb_user_log
#     union 
#     select uid,date(out_time)
#     from tb_user_log
#   ) t
#   group by dt
# ), -- 日活表
# new_user_t as (
#   select min_dt,count(uid) new_cnt
#   from (
#     SELECT uid,min(date(in_time)) min_dt
#     from tb_user_log
#     group by uid
#   ) t
#   group by min_dt
# )  -- 用户第一次登陆表
# select dau_t.dt,dau,if(round(new_cnt/dau,2) is null,0.00,round(new_cnt/dau,2)) uv_new_ratio
# from dau_t 
# left join new_user_t
# on dau_t.dt=new_user_t.min_dt

# 方法2：求每个用户按照日期先后的累计和或者排名
# 1.求出每个用户按照日期先后的累计和或者排名
# select *,rank() over (partition by uid order by date ) as times
# from
# (select uid,left(in_time,10) as date
# from tb_user_log
# union 
# SELECT uid,left(out_time,10) as date
# from tb_user_log) tmp
# # 2.按照日期分组计算指标
# select date,count(uid) as dau,
#     round( sum(if(times=1,1,0))/count(uid) ,2)
# from
# (select *,rank() over (partition by uid order by date ) as times
# from
# (select uid,left(in_time,10) as date
# from tb_user_log
# union 
# SELECT uid,left(out_time,10) as date
# from tb_user_log) tmp) base
# GROUP by date
# order by date

代码借鉴@通辽可汗克鸽勃 ，在此表示感谢！