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

select t0.date,
       ifnull(round(count(t2.user_id)/count(t1.user_id),3),0) p
from(
    select date
    from login
    group by date
) t0
left join 
(
    select user_id,min(date) as date
    from login
    group by user_id) t1 
on t0.date = t1.date
left join login t2 on t1.user_id = t2.user_id and t2.date = date_add(t1.date,interval 1 day)
group by t0.date

没转换过来思路，本质是求新用户次日登录的情况，所以只需要筛选出所有新用户，然后进行连接