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

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