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

--  注意同一个用户/设备每天可能有多次记录,需要先去重 
-- 平均次日留存率=每日使用后次日留存用户数/总用户数
    --  判断次日留存：用户使用日期+1也是用户使用日期
        -- 方法一：用户使用日期+1在用户活跃表里
        -- 方法二：用户使用日期的下一行（排序后）等于使用日期+1
-- 方法二： 窗口函数
select  
    round(
        count(case when dt_next=date_add(dt, interval 1 day) then 1 else null end) -- 计算次日留存用户：判断用户活跃日期的下一行是否也是用户活跃日期 
        /count(dt) -- 计算总用户数
    ,4) as avg_ret
from
( -- 用户活跃日期和下一行
    select 
            device_id
            , dt -- 用户活跃日期
            , lead(dt,1)over(partition by device_id order by dt) as dt_next -- 用户活跃日期的下一行
    from 
    (-- 去重用户和日期
        select 
            distinct device_id
            , date as dt
        from  question_practice_detail 
    ) t11
) t1