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

WITH user_t AS (
    SELECT uid, DATE_FORMAT(in_time,'%Y-%m-%d') AS timea
    FROM tb_user_log
    UNION 
    SELECT uid, DATE_FORMAT(out_time,'%Y-%m-%d') AS timea
    FROM tb_user_log
    ), user_t2 AS
    (
    SELECT DISTINCT *, 
    ROW_number() OVER (PARTITION BY uid ORDER BY timea) AS user_day
    FROM user_t 
    )

SELECT uf.timea AS dt,
ROUND(COUNT(ut2.uid) / COUNT(uf.uid), 2) AS uv_left_rate
FROM(
    SELECT *
    FROM user_t2
    WHERE user_day =1
    ) AS uf
LEFT JOIN user_t2 ut2 
ON ut2.uid = uf.uid AND DATE_ADD(uf.timea, INTERVAL 1 day) = ut2.timea
WHERE DATE_FORMAT(uf.timea, '%Y-%m') = '2021-11'
GROUP BY uf.timea
ORDER BY dt

注意找的是新用户第二天的留存率，我用了row_number 唉 太不精简了

我看有大佬直接用的min（date） 感觉好神/(ㄒoㄒ)/~~

https://blog.nowcoder.net/n/73d6ca1d59314da6986453ff76be8d38