计算某日新增用户，及其次日、3日、3日内的留存率

计算某日新增用户，及其次日、3日及其3日内的留存率

user_id 用户

dt 时间

login 表名

select 
    login_day,
    count(distinct t.user_id) as uv,
    count(distinct if(datediff(t.dt,s.dt)=1,t.user_id,null))/count(distinct t.user_id) as rt_1,
    count(distinct if(datediff(t.dt,s.dt)=3,t.user_id,null))/count(distinct t.user_id) as rt_3,
    count(distinct if(datediff(t.dt,s.dt)<=3,t.user_id,null))/count(distinct t.user_id) as rt_3_n
from(
    select user_id, min(dt) as login_day
    from login
    group by user_id
) t
left join login s
on t.user_id = s.user_id
group by login_day

计算某日的次日、3日及其3日内的留存率

select 
    dt,
    count(if(datediff(next_dt,dt)=1,user_id,null))/count(user_id) as rt_1,
    count(if(datediff(third_dt,dt)=3,user_id,null))/count(user_id) as rt_3,
    count(if(datediff(th_dt,dt)<=3,user_id,null))/count(user_id) as rt_3_n
from(
    select 
        user_id,
        dt,
        lead(dt,1) over(partition by user_id order by dt) as next_dt,
        lead(dt,3) over(partition by user_id order by dt) as third_dt,
        lead(dt,1) over(partition by user_id order by dt) as th_dt,
    from login
) t
group by dt