select e.date,(h/g) 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<3) 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

输出:
2020-10-12|0.6667
2020-10-14|0.5000
还差一点,0的没输出来