题解 | #牛客每个人最近的登录日期(三)#

1.将第二天日期拼到表后

select *,date_add(min(date) over (partition by user_id),interval 1 day) d from login

2.筛选在第二天登陆的新用户

select *
from (select *,date_add(min(date) over (partition by user_id),interval 1 day) d from login) l
where date=d

3.计算所有用户数量

select count(distinct user_id) from login)

4.p=在第二天登陆的新用户/所有用户

select round(count(*)/(select count(distinct user_id) from login),3)
from (select *,date_add(min(date) over (partition by user_id),interval 1 day) d from login) l
where date=d