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

#比较简单的一种思路了。

with tiaojian as (
select 
uid,
login_date,
lead(login_date)over(partition by uid order by login_date) as diff,
row_number()over(partition by uid order by login_date) as pm
from user_login_tb
)

select 
login_date,
round(
sum(case when datediff(diff,login_date)=1 then 1 else 0 end )/count(distinct uid),2) as uv_left_rate,
round(
sum(case when pm=1 and datediff(diff,login_date)=1 then 1 else 0 end )/count(distinct case when pm=1 then uid end),2) as new_uv_left_rate
from tiaojian
group by login_date