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

SELECT
    count(date2) / count(date1) avg_ret
from
    (
        select distinct
            a.device_id,
            a.date date1,
            b.date date2
        from
            question_practice_detail a
            left join question_practice_detail b on DATEDIFF (b.date, a.date) = 1
            and a.device_id = b.device_id
    ) t;

将同一设备号前一天日期和后一天日期连接起来,从这个表中就可以看出来了,a.device_id删掉后不给通过,想了好久想不通