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

# 首先用union解决跨天问题，然后开窗记录下一个登录日
WITH t1 AS (
    SELECT
        uid
        ,DATE(in_time) AS dt
    FROM
        tb_user_log
    UNION
    SELECT
        uid
        ,DATE(out_time) AS dt
    FROM
        tb_user_log
),
t2 AS (
    SELECT
        uid
        ,dt
        ,LEAD(dt) OVER (PARTITION BY uid ORDER BY dt) next_day
    FROM
        t1
)

SELECT
    dt
    ,ROUND(AVG(CASE WHEN DATEDIFF(next_day, dt) = 1 THEN 1 ELSE 0 END), 2) AS uv_left_rate
FROM
    t2 a
# 这里要注意，不能直接在上面把10月筛选掉，会影响11.01这一天的判断，要在这里把10月的记录筛掉
# 同时利用not exists筛掉非新用户
WHERE 
    NOT EXISTS (SELECT 1 FROM t2 b WHERE a.uid = b.uid AND a.dt > b.dt)
    AND DATE_FORMAT(dt, '%Y%m') = '202111'
GROUP BY
    dt
ORDER BY 
    dt