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

-- 建立活跃用户表
WITH act_user AS (
    SELECT uid,
        DATE(in_time) AS dt
    FROM tb_user_log
    UNION
    SELECT uid,
        DATE(out_time) AS dt
    FROM tb_user_log
)

SELECT dt, dau,
    ROUND(IF(new_user_cnt IS NULL, 0, new_user_cnt / dau), 2) AS uv_new_ratio
FROM (
    -- 计算当天的活跃用户数
    SELECT dt,
        COUNT(DISTINCT uid) AS dau
    FROM act_user
    GROUP BY dt
) t_act_user
LEFT JOIN (
    SELECT dt,
        COUNT(DISTINCT uid) AS new_user_cnt
    FROM (
        -- 计算用户的最早活跃日期
        SELECT uid,
            MIN(DATE(in_time)) AS dt
        FROM tb_user_log
        GROUP BY uid
    ) first_act_day
    GROUP BY dt
)new_user USING(dt)
ORDER BY dt