留客率：留客成功date-lag(date)=-1 筛选新用户date=min(date)

select round(sum(case l_date when -1 then 1 else 0 end)/count(*),3)
from(
select date,min(date)over(partition by user_id) as m_d##每个用户第一次登陆时间##,date-lag(date)over(partition by user_id order by date desc##此处对日期排倒序##) as l_date 
## lag按照每个用户分组，倒序后取第二天日期，若连续 date-lag(date)=-1;否则不等于-1
from login
) as l
where date=m_d 
##日期=每个用户第一次登陆日期，筛除非第一天登录的连续数据