select e.date,round((h/g),3) as p from
(select date,count(user_id) as g from
(select * from
(select *,rank() over(partition by user_id order by date) as rk from login ) as A
where rk=1) as B
group by date) as e
join
(select c.date,count(c.date) as h from
(select * from
(select *,rank() over(partition by user_id order by date) as rk from login
) as A
where rk<3
)c,
(select * from
(select *,rank() over(partition by user_id order by date) as rk from login
) as b
where rk<3
)d
where c.user_id=d.user_id and datediff(d.date,c.date)=1
group by c.date
) as f on e.date=f.date
union
select distinct date,0 as p
from login where date not in (select c.date as h from
(select * from
(select *,rank() over(partition by user_id order by date) as rk from login
) as A
where rk<3
)c,
(select * from
(select *,rank() over(partition by user_id order by date) as rk from login
) as b
where rk<3
)d
where c.user_id=d.user_id and datediff(d.date,c.date)=1
group by c.date
)
order by date

每个用户的第一天的数据表join对应的第二天的数据牛，简单来说就是第一天与后一天连续的次数