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

select
    t1.dt,
    if (
        count(distinct t2.user_id) = 0,
        0.000,
        round(
            count(
                case
                    when datediff (t3.date, t1.dt) = 1 then t3.user_id
                end
            ) / count(distinct t2.user_id),
            3
        )
    ) as p
from
    (
        select
            date as dt
        from
            login
        group by
            date
    ) t1
    left join (
        select
            user_id,
            min(date) as min_dt
        from
            login
        group by
            user_id
    ) t2 on t1.dt = t2.min_dt
    left join login t3 on t2.user_id = t3.user_id
group by
    t1.dt