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

# 计算日活的时候也要算out_time,有可能是在午夜12点左右都刷了,用UNION自动去重!!!

WITH t1 AS (SELECT
uid,
DATE(in_time) AS dt
FROM tb_user_log
UNION
SELECT
uid,
DATE(out_time) AS dt
FROM tb_user_log
),
t3 AS(
SELECT
dt,
COUNT(DISTINCT uid) AS dau
FROM t1
GROUP BY dt
),
t2 AS
(SELECT 
uid,
MIN(DATE(in_time)) AS dt
FROM tb_user_log
GROUP BY uid)

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