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

WITH t1 AS (
    SELECT uid,
           in_time
    FROM tb_user_log
    UNION ALL
    SELECT uid,
           out_time AS in_time
    FROM tb_user_log
),
t2 AS (
    SELECT uid,
           in_time,
           RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1
    FROM t1
),
t3 AS (
    SELECT uid,
           DATE (in_time) AS dt,
           DATE_ADD(DATE(in_time),INTERVAL 1 DAY ) AS dt_next,
           num_1
    FROM t2
    WHERE num_1 = 1 AND DATE (in_time) BETWEEN '2021-11-01' AND '2021-11-30'
),
t4 AS (
    SELECT dt,
           count(uid) AS new_num
    FROM t3
    GROUP BY dt
),
t5 AS (
    SELECT t3.uid,
           t3.dt,
           t3.dt_next,
           DATE(t2.in_time) AS dt_last
    FROM t3
    LEFT JOIN t2
    ON t3.uid = t2.uid AND t3.dt_next = DATE(t2.in_time)
    GROUP BY t3.uid,
           t3.dt,
           t3.dt_next,
           dt_last
),
t6 AS (
    SELECT dt,
           count(dt_last) AS stop_num
    FROM t5
    GROUP BY dt

),
t7 AS (
    SELECT t4.dt,
           stop_num,
           new_num,
           ROUND(stop_num / new_num, 2) AS uv_left_rate
    FROM t6
    LEFT JOIN t4
    ON t6.dt = t4.dt
),
t8 AS (
    SELECT dt, uv_left_rate
    FROM t7
    ORDER BY dt ASC
)
SELECT * FROM t8;

# 关键在于先排序筛选新用户，再进行时间范围切割！