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

with 
t1 as (select distinct device_id,date from question_practice_detail),
t2 as (
    select *,
        row_number() over (partition by device_id order by date) as rn
    from t1
),
t3 as (
    select *,
        date_sub(date ,interval rn day) as temp
    from t2
),
t4 as (
	select device_id,temp,
    count(1) as time
    from t3
    group by device_id,temp
)
select (sum(if(time = 2 ,1,0)) + sum(if(time = 3 ,2,0)) )/sum(time) as avg_ret
from t4;