# 每个日期新用户的次日留存率。
SELECT a1.date,
ifnull(round(sum(if(a3.user_id is not null,1,0))/count(a2.user_id),3),0) as p
FROM
(
SELECT date 
from login
group by date
) as a1
left outer join
(select user_id,min(date) as new_date
from login
group by user_id
) as a2 on a1.date=a2.new_date
left outer join
(
select user_id,date
from login
) as a3 on a2.user_id=a3.user_id and a2.new_date=date_sub(a3.date,interval 1 DAY)
group by a1.date
order by a1.date asc