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

--求出每日新增用户
with new_user as(
    select uid,date(min(in_time)) dt
    from tb_user_log
    group by uid
),
-- 求出全部用户信息
all_user_log as(
    select uid,date(in_time) dt
    from tb_user_log
    union
    select uid,date(out_time)
    from tb_user_log
)
select all_user_log.dt dt,
       count(all_user_log.uid) dau,
       round(count(new_user.uid)/count(all_user_log.uid),2) uv_new_ratio  
from new_user
right join all_user_log
on new_user.uid=all_user_log.uid
and new_user.dt=all_user_log.dt
group by all_user_log.dt
order by all_user_log.dt