思路：次日留存率 = 这些新用户中次日登录的用户数/该日新登录用户数
select
date
,round(ifnull(sum(case when f_d = date and l_d - date = 1 then 1 else 0 end)
/sum(case when f_d = date then 1 else 0 end),0),3) p
from
(
select
user_id
,date
,first_value(date)over(partition by user_id order by date) f_d
,lead(date,1)over(partition by user_id order by date) l_d
from login
) a
group by 1
order by 1