题解 | #日活次日留存率和新户次日留存率#

With Login_Tb AS 
(
    select
        a.uid,
        a.login_date,
        case when a.login_date = b.first_login_date then 'new' else 'old' end as login_type
    from
        user_login_tb a
    left join
        (select uid,min(login_date) as first_login_date from user_login_tb group by uid) b on a.uid = b.uid
    where
        a.login_date between '2022-08-01' and '2022-08-31'
),
Second_Login_Tb AS
(
    select
        a.uid,
        b.login_date as second_login_date
    from
        Login_Tb a
    left join
        user_login_tb b on datediff(b.login_date,a.login_date) = 1 and a.uid = b.uid
    where
        b.login_date between '2022-08-01' and '2022-08-31'
)
select
    lt.login_date,
    round(count(distinct sl.uid)/count(distinct lt.uid),2) as uv_left_rate,
    round(count(distinct case when lt.login_type = 'new' then sl.uid else null end)/count(distinct case when lt.login_type = 'new' then lt.uid else null end),2) as new_uv_left_rate
from    
    Login_Tb lt
left join
    Second_Login_Tb sl on lt.uid = sl.uid and datediff(sl.second_login_date,lt.login_date) = 1
group by 
    lt.login_date
order by 
    lt.login_date