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

SELECT
    round(
        sum(
            CASE
                WHEN p2.device_id IS NOT NULL THEN 1
                ELSE 0
            END
        ) / count(*),
        4
    ) AS avg_ret
FROM
    (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) p1
    LEFT JOIN (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) p2 ON p1.device_id = p2.device_id
    AND p2.date = Date_ADD(p1.date, INTERVAL 1 DAY)