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

select
    sum(dc.d) / count(dc.d) avg_ret
from
    (
        select
            device_id,
            case
                when date - lag (date, 1) over (
                    partition by
                        device_id
                    order by
                        date
                ) = 1 then 1
                else 0
            end d
        from
            question_practice_detail
        group by
            device_id,
            date
        order by
            device_id,
            date
    ) dc

1、建立子查询：date-lag窗口函数，以device_id分区，以date排序，得到时间差，用case when对时间差为1的计数1，其他计数0

2、对子查询中的时间差列求和，得到连续刷题的个数，对子查询中的时间差列计数得到刷题总数，相除即可得到结果