题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
WITH reg AS (
SELECT
uid,
MIN(dt) AS reg_dt
FROM (SELECT
uid,
DATE_FORMAT(in_time, '%Y-%m%-%d') AS dt
FROM tb_user_log
UNION
SELECT
uid,
DATE_FORMAT(out_time, '%Y-%m%-%d') AS dt
FROM tb_user_log ) AS a
GROUP BY uid ),
total AS (SELECT
uid,
DATE_FORMAT(in_time, '%Y-%m%-%d') AS dt
FROM tb_user_log
UNION
SELECT
uid,
DATE_FORMAT(out_time, '%Y-%m%-%d') AS dt
FROM tb_user_log
)
SELECT
reg_dt AS dt,
ROUND(SUM(IF(dt = DATE_ADD(reg_dt, INTERVAL 1 DAY), 1 , 0))/SUM(IF(dt = reg_dt, 1 , 0)),2) AS uv_left_rate
FROM total
LEFT JOIN reg
USING (uid)
WHERE reg_dt BETWEEN '2021-11-01' AND '2021-11-30'
GROUP BY reg_dt
ORDER BY dt
