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

SELECT 
    AVG(CASE WHEN t3.device_id IS NOT NULL THEN 1 ELSE 0 END) AS avg_ret
FROM (
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
) t2
LEFT JOIN (
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
) t3
ON t2.device_id = t3.device_id
   AND DATEDIFF(t3.date, t2.date) = 1;