题解 | 计算用户的平均次日留存率

WITH base AS (
    SELECT
        device_id,
        DATE(date) AS practice_date,
        RANK() OVER (
            PARTITION BY device_id
            ORDER BY DATE(date)
        ) AS rk
    FROM question_practice_detail
    GROUP BY device_id, DATE(date)
)
SELECT
    ROUND(
        SUM(
            CASE
                WHEN DATEDIFF(b2.practice_date, b1.practice_date) = 1 THEN 1
                ELSE 0
            END
        ) / COUNT(*),
        4
    ) AS avg_ret
FROM base b1
LEFT JOIN base b2
    ON b1.device_id = b2.device_id
   AND b2.rk = b1.rk + 1;