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

SELECT
    COUNT(date2) / COUNT(date1) avg_ret
FROM(
    SELECT
        DISTINCT qpd.device_id,
        qpd.date date1,
        tb.date date2
    FROM
        question_practice_detail qpd LEFT JOIN (
            SELECT
                DISTINCT device_id,
                date
            FROM
                question_practice_detail
        ) tb ON
        qpd.device_id = tb.device_id AND
        DATE_ADD(qpd.date, INTERVAL 1 DAY) = tb.date
) tb2;