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

# 用户注册时间
WITH t1 AS (
    SELECT TB.* ,T1.dt
    FROM tb_user_log TB
    LEFT JOIN 
    (
        SELECT t.uid, MIN(DATE(t.in_time)) AS dt
        FROM tb_user_log t
        GROUP BY t.uid
        ORDER BY dt ASC
    ) T1
    ON TB.uid = T1.uid
),
 RT AS (
select  t2.action_time
,count(distinct t2.uid) as dau
from t1 left join 
##用户活跃时间
(
select uid,date(in_time) as action_time from tb_user_log
union 
select uid,date(out_time) as action_time  from tb_user_log) t2
on t1.uid=t2.uid
group by t2.action_time)

select RT.*,ifnull(round(t3.dau/RT.dau,2),0) as  uv_new_ratio
from RT 
left join 
(select  t2.action_time
,count(distinct t2.uid) as dau
from t1 left join 
(
select uid,date(in_time) as action_time from tb_user_log
union 
select uid,date(out_time) as action_time  from tb_user_log) t2
on t1.uid=t2.uid
WHERE t1.dt=t2.action_time
group by t2.action_time) t3
on RT.action_time=t3.action_time
;