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

with cte as (
select *,min(login_date)over(partition by uid) as reg_date,
         lead(login_date)over(partition by uid) as next_date
from (select distinct uid,login_date from user_login_tb) a )
select login_date ,
       round(count(datediff(next_date,login_date)=1 or null)/count(*),2) as uv_left_rate,
       round(count(datediff(next_date,login_date)=1 and reg_date=login_date or null)/
       count(reg_date=login_date or null),2)  as new_uv_left_rate
from cte
group by login_date
order by login_date 

用到开窗函数和临时表之后就不用再连接了