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

select
    date,
    IFNULL(round(sum(if (rk=1 and diff = 1, 1, 0)) / sum(if (rk = 1, 1, 0)),3), 0.000) p
from
    (
     select
        user_id,
        date,
        datediff (lead (date, 1, '9999-01-01') over ( partition by user_id order by date),date)diff,
        row_number() over (partition by user_id order by date) rk
        from
            (
                select distinct user_id,date
                from login
            ) t1
    ) t2
group by date