逻辑易理解

#1.建立临时表，计算date+1
with t as (
    select *,lead(date,1)over(partition by user_id order by date) as dtime
    from login
)

#2.首先筛选出用户第一天登录的数据，再利用sum和datediff函数求出dtime和date相差为1的数据量

select round(sum(datediff(date,dtime)=-1)/count(distinct user_id),3)
from t
where (user_id,date) in (select user_id,min(date) from t group by user_id)