题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
select x.date,ifnull(y.p,0) from(select distinct date from login) x left join
(select a.date,round(sum(case when b.user_id is not null then 1 else 0 end)/count(*),3) p
from login a
left join login b on a.user_id=b.user_id and b.date=DATE_ADD(a.date,INTERVAL 1 DAY)
where (a.user_id,a.date)=
(select user_id,min(date) from login where user_id=a.user_id group by user_id)
group by a.date) y on x.date=y.date