题解 | #2021年11月每天新用户的次日留存率#

WITH t_union as(
SELECT DISTINCT uid,
DATE_FORMAT(in_time,'%Y-%m-%d') AS dt
FROM tb_user_log
UNION 
SELECT DISTINCT uid,
DATE_FORMAT(out_time,'%Y-%m-%d') AS dt
FROM tb_user_log
),
t as(
SELECT uid,dt,
MIN(dt)OVER(PARTITION BY uid) AS new_dt,
LEAD(dt)OVER(PARTITION BY uid ORDER BY dt) AS next_dt
FROM t_union
),
t_cal as(
SELECT dt,
SUM(IF(dt = new_dt AND DATEDIFF(next_dt,dt) = 1,1,0)) AS active_num,
SUM(IF(dt = new_dt,1,0)) AS total_num
FROM t
GROUP BY dt
HAVING DATE_FORMAT(dt,'%Y-%m') = '2021-11'
)

SELECT dt,
ROUND(active_num / total_num,2) AS uv_left_rate
FROM t_cal
WHERE total_num AND active_num IS NOT NULL

ORDER BY dt ASC;