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

SELECT
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
    (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) AS q1
    LEFT JOIN (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) AS q2 ON q1.device_id = q2.device_id
    AND DATEDIFF(q2.date, q1.date) = 1;