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

-- 开窗函数解法 与连续登录思路大致

select
    -- 求出连续俩天作答的人数
    count(device_id) / (
        -- 求出总作答人数，要去重
        select
            count(distinct device_id, date)
        from
            question_practice_detail
    ) as avg_ret
from
    (
        select
            device_id,
            date,
            -- 用 开窗函数 对date 开窗 求出连续日期
            lead (date, 1, '9999-12-01') over (
                partition by
                    device_id
                order by
                    date asc
            ) next_day
        from
            ( -- 对同一设备同一日期冲服的数据去重
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) t1
    ) t2
where
    datediff (next_day, date) = 1 -- 下一行 与 当前行 日期相差为 1 即为连续俩天登录