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

SELECT AVG(if(DATEDIFF(date2, date1)= 1,1,0)) as avg_ret
FROM
(
    SELECT device_id, date as date1,
    LEAD(date) OVER 
    (
        PARTITION BY device_id 
        ORDER BY date
    ) as date2 --当前活跃日的后一个活跃日
    FROM
    (
        SELECT distinct device_id, date 
        FROM question_practice_detail
    )uniq --去除同一天多次答题的重复数据
)final

思路

1. 一个用户可能在同一天有多条答题记录，而我们只关心是否答题，因此需要构造uniq表代表该用户当天是否答题；
2. 次日留存率 = 在基准天的第二天答题的用户数 / 基准天答题的用户数，计算方法可分为两种情况：

• 仅考虑新用户的留存率，则用COUNT函数分别计算分子和分母，其中分母要保证是新用户（第一条记录）；
• 需要考虑所有用户的留存率，则直接用AVG函数对基准日求平均留存率，因为对分母没有任何限制（所有记录），分子是记录次日是否活跃的0-1布尔变量。

Q：为什么AVG(if(DATEDIFF(date2, date1)= 1,1,0))即为平均留存率？

A: IF(DATEDIFF(date2, date1)=1, 1, 0)返回的是一个0-1序列，例如：用户1:[0,1,1]，对其求AVG的结果 = 元素之和/元素总数，即2/3，因此可以直接得到平均次日留存率（留存为1，不留存为0）

知识点

1. IF(exp, a, b)：如果exp为真则=a，否则=b；
2. LEAD(column)OVER(PARTITION BY group, ORDER BY date)：取按group分组、按date降序排列的下一条column记录。