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

WITH Active_Date AS(
    SELECT uid, dt, DENSE_RANK()OVER(PARTITION BY uid ORDER BY dt) rk
    FROM ((SELECT DISTINCT uid, date(in_time) dt FROM tb_user_log) 
    UNION
    (SELECT DISTINCT uid, date(out_time) dt FROM tb_user_log)
    ) sub
),
Retention AS(
    SELECT uid, dt, rk, CASE WHEN DATE_ADD(dt, INTERVAL 1 DAY)=LEAD(dt,1)OVER(PARTITION BY uid ORDER BY rk) THEN 1 ELSE 0 END if_retention  
    FROM Active_Date
)

SELECT dt, ROUND(SUM(if_retention)/COUNT(*), 2)
FROM Retention
WHERE rk = 1 AND dt LIKE '2021-11%'
GROUP BY dt