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

WITH t1 as( -- 列出所有的活跃日期
    SELECT uid,date(in_time) dt FROM tb_user_log
    UNION ALL
    SELECT uid,date(out_time) dt FROM tb_user_log
),t2 as( -- 求出最早的活跃日期(注册日期)
    SELECT uid,min(dt) md FROM t1 group by uid order by uid
),t3 as(
    select
           #group_concat(DISTINCT t1.uid),
           dt,
           count(DISTINCT t1.uid) 'dau' -- 统计每天的活跃用户id
    FROM t1
    group by dt
    order by dt
),t4 as(
    SELECT dt,count(uid) 'reg_num' -- 注册时间该天的新用户数量
    FROM t3
    left JOIN t2
    ON t2.md = t3.dt
    group by t3.dt
)
SELECT t3.*,
       round(reg_num/dau,2)
       FROM t3 left join t4
ON t3.dt = t4.dt;