select date,
ifnull(round((sum(case when 
(user_id,date)in(select user_id,date_add(date,interval -1 day) from login)--给login中所有日期都减一，判断是否在其中，即判断用户第二天也登录
and (user_id,date)in (select user_id,min(date)from login group by user_id)--login中新用户及其登录日期
then 1 else 0 end))--两者都成立即是次日登录过的新用户
/sum(case when (user_id,date) in (select user_id,min(date) 
from login group by user_id ) then 1 else 0 end),3),0)--当日的新用户总数
from login
group by date
order by date