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

-- 思路：

-- 1.构造用户活跃表：跨天数据，算作两条活跃记录

-- 在拿到用户活跃日期和最早活跃日期即可区分新旧用户

-- 2. 按日期分组统计dau和新用户占比

-- 新用户判断逻辑：if(dt=first_dt,1, 0)

-- 思路：
--      1.构造用户活跃表：跨天数据，算作两条活跃记录
--          在拿到用户活跃日期和最早活跃日期即可区分新旧用户
--      2. 按日期分组统计dau和新用户占比
--          新用户判断逻辑：if(dt=first_dt,1, 0)
with  t_active as(
-- 1.构造用户活跃表：跨天数据，算作两条活跃记录
--      在拿到用户活跃日期和最早活跃日期即可区分新旧用户
select 
    uid, 
    date(in_time) as dt,
    date(min(in_time) over(partition by uid)) as first_dt
from tb_user_log
union -- 为防止出现跨天数据，需要根据in_time和out_time union合并去重一下
select 
    uid, 
    date(out_time) as dt,
    date(min(in_time) over(partition by uid)) as first_dt
from tb_user_log
)

-- 2. 按日期分组统计dau和新用户占比
--  新用户判断逻辑：if(dt=first_dt,1, 0)
select 
    dt,
    count(distinct uid) dau,
    round(sum(if(dt=first_dt,1, 0)) / count(distinct uid), 2) as uv_new_ratio
from t_active
group by dt 
order by dt