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

select 
    b.date,
    round(sum(if((login.date is not null),1,0))/sum(if((a.date is not null),1,1)),3) p
from (select distinct date from login) b # 用于取时间
# 取出新用户
left join(select user_id,min(date) date from login group by user_id) a 
on b.date = a.date
left join login on login.user_id = a.user_id 
and date_sub(login.date,interval 1 day) = a.date
group by b.date
order by b.date