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

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 q2.date=date_add(q1.date,interval 1 day) 

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

原来把AND DATEDIFF(q2.date, q1.date) = 1放在q1.device_id = q2.device_id 后面，就能把date认定为是同一个device_id的人的日期。