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

select
    l.date,
    round(if((count(distinct n.user_id)) != 0,count(distinct m.user_id)/(count(distinct n.user_id)),0),3) as p
from
    login l 
    left join 
    (
        select
            user_id,
            min(date) date
        from
            login
        group by
            user_id
    ) n on l.date = n.date
    left join login m
    on  m.date = DATE_ADD(l.date,INTERVAL 1 DAY) and m.user_id = n.user_id
group by
    date
order by
    date