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

with min_login as (
    select
        user_id,
        min(date) as min_date
    from 
        login
    group by 
        user_id
)

select
    round((sum(case 
            when l.date = date_add(m_l.min_date,interval 1 day) then 1 
            else 0
        end ) / count(distinct m_l.user_id)),3) as p
from 
    login as l
inner join 
    min_login as m_l
on 
    l.user_id = m_l.user_id