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

select
    round(avg(if(t2.device_id is not null, 1, 0)), 4) as avg_ret
from
    (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) t1
    left join (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) t2 on t1.device_id = t2.device_id
    and t2.date = date_add(t1.date, interval 1 day)

先进行数据去重，就是子查询

SELECT DISTINCT device_id, date FROM question_practice_detail

然后自连接，连接条件：

ON t1.device_id = t2.device_id AND t2.date = DATE_ADD(t1.date, INTERVAL 1 DAY)

关键点：因为是左连接（LEFT JOIN)， 即使明天没来，今天的记录（t1）也会保留下来，只是右边是空的。这保证了分母（总活跃时间数）不会丢失。

然后标记留存状态：

IF(t2.device_id IS NOT NULL, 1, 0)