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
用到开窗函数和临时表之后就不用再连接了