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

WITH t1 AS(
    SELECT uid,MIN(DATE(in_time)) AS dt
    FROM tb_user_log
    GROUP BY uid
),  # 新用户表
t2 AS (
SELECT uid, DATE(in_time) as active_time
FROM tb_user_log
UNION
SELECT uid, DATE(out_time) as active_time
FROM tb_user_log
UNION
SELECT uid, (DATE(in_time)+ INTERVAL 1 DAY) as active_time   # 跨天活跃
FROM tb_user_log
WHERE TIMESTAMPDIFF(DAY,in_time,out_time)>=1
)  # 用户活跃日期表

SELECT t2.active_time, COUNT(t2.uid) AS dau,
       ROUND(COUNT(t1.uid)/COUNT(t2.uid),2) AS uv_new_ratio
FROM t2
LEFT JOIN t1 ON t1.uid = t2.uid and t1.dt = t2.active_time
GROUP BY active_time
ORDER BY active_time