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

with a1 as (
    select distinct device_id,date
    from question_practice_detail
), 
a as(
    select device_id,date,count(*) over(partition by device_id order by date range between current row and interval 1 day following) as ct
    from a1
),
a2 as (
    select count(*) as ct1
    from a
    where a.ct = 2
),
a3 as (
    select count(*) as ct2
    from a1
)

select ct1/ct2 as avg_ret
from a2,a3

窗口函数秒