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

select round(sum(1_day_reten)/sum(total_users),3) as p from

(select 
    count(distinct a.user_id) as total_users,
    count(distinct case 
                when datediff(b.date, a.date) = 1 then a.user_id end) as 1_day_reten
from
    (select 
        distinct user_id, min(date) as date
    from 
        login
    group by 
        user_id
    ) a
left join   
    (select 
        distinct user_id, date
    from login
    ) b 
on
    a.user_id = b.user_id and a.date <= b.date
group by 
    a.date
) temp