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

WITH base AS (
    SELECT
        device_id,
        practice_date,
        LEAD(practice_date) OVER (
            PARTITION BY device_id
            ORDER BY practice_date
        ) AS next_date
    FROM (
        SELECT
            device_id,
            DATE(date) AS practice_date
        FROM question_practice_detail
        GROUP BY device_id, DATE(date)
    ) t
)
SELECT
    ROUND(
        SUM(CASE WHEN DATEDIFF(next_date, practice_date) = 1 THEN 1 ELSE 0 END)
        / COUNT(*),
        4
    ) AS avg_ret
FROM base;