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

# x日留存率的解法，注意在前面筛选阶段直接只取11月有可能有定义上的问题，比如10-31和11-01这两天登录的客户算不算11月的新用户等，也可能影响需要10-31数据来判断次日留存的但却被提前删去了的情况
SELECT
    first_dt AS dt
    ,ROUND(SUM(CASE WHEN DATEDIFF(dt, first_dt) = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT a.uid), 2) AS uv_left_rate 
FROM
    (
    SELECT 
        uid
        ,MIN(DATE_FORMAT(in_time, '%Y-%m-%d')) first_dt
    FROM
        tb_user_log
    GROUP BY
        uid
    ) a
    LEFT JOIN
    (
    SELECT uid
        ,DATE_FORMAT(in_time, '%Y-%m-%d') dt
    FROM 
        tb_user_log
    UNION
    SELECT uid
        ,DATE_FORMAT(out_time, '%Y-%m-%d') dt
    FROM 
        tb_user_log
    ) b
    ON a.uid = b.uid
GROUP BY 
    first_dt
HAVING
    DATE_FORMAT(first_dt, '%Y-%m') = '2021-11' 
ORDER BY
    first_dt