题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
WITH total AS(
SELECT
uid,
DATE(in_time) AS dt
FROM
tb_user_log
UNION
SELECT
uid,
DATE(out_time) AS dt
FROM
tb_user_log),
reg AS(
SELECT
uid,
DATE(MIN(in_time)) AS reg_date
FROM
tb_user_log
GROUP BY uid)
SELECT
dt,
COUNT(*) AS dau,
ROUND(COUNT(reg_date = dt OR NULL)/COUNT(*), 2) AS uv_new_ratio
FROM total
LEFT JOIN reg
USING(uid)
GROUP BY dt
ORDER BY dt
