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

SELECT 
    ROUND(AVG(rt.r),4)
FROM(
    SELECT
            COUNT(q2.device_id) / COUNT(q1.device_id) AS r
        FROM
            question_practice_detail q1
            LEFT JOIN question_practice_detail q2 ON q1.device_id = q2.device_id
            AND q2.date = q1.date + 1
        GROUP BY
            q1.device_id, q1.date
) AS rt;