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

WITH daily_activity AS (  -- 定义一个名为 daily_activity 的公共表表达式，用于获取不同设备和日期的记录
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
),

activity_with_lead AS (  -- 再定义一个名为 activity_with_lead 的公共表表达式
    SELECT 
        device_id,  -- 选择设备 ID
        date,  -- 选择日期
        LEAD(date) OVER (PARTITION BY device_id ORDER BY date) AS next_date  -- 通过窗口函数获取每个设备按日期排序的下一个日期
    FROM 
        daily_activity
)

SELECT 
    ROUND(AVG(CASE  -- 计算平均值并四舍五入到 4 位小数
        WHEN DATEDIFF(next_date, date) = 1 THEN 1.0  -- 如果下一个日期与当前日期相差 1 天，值为 1
        ELSE 0  -- 否则为 0
    END), 4) AS avg_ret  -- 将结果命名为 avg_ret
FROM 
    activity_with_lead;