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

select
    l1.date,
    ifnull(round(count(distinct l3.user_id) / count(l2.user_id),3),0) as p
from (
    select
        date
    from 
        login
    group by 
        date
) as l1
left join (
    select
        user_id,
        min(date) as date
    from 
        login
    group by 
        user_id
) as l2
on  
    l1.date = l2.date
left join 
    login as l3
on 
    l2.user_id = l3.user_id and datediff(l3.date,l2.date) = 1
group by 
    l1.date
order by 
    l1.date