1.獲得新用戶的登陸時(shí)間
select user_id,min(date) first_date
from login
group by user_id;t2

2.獲得有第二天有老用戶登陸的日期和人數(shù)
select distinct l1.date,count(l1.user_id) savenum
from login l1
right join login l2
on l1.user_id=l2.user_id
where l2.date=date_add(l1.date,interval 1 DAY)
group by l1.date;t1

3.連接t2、t1,能夠獲得日期對(duì)應(yīng)的 (老用戶人數(shù)/新用戶人數(shù)) 即p
select distinct t2.first_date ,round(savenum/count(t2.first_date),3) p
from (select user_id,min(date) first_date
from login
group by user_id)t2
join (select distinct l1.date,count(l1.user_id) savenum
from login l1
right join login l2
on l1.user_id=l2.user_id
where l2.date=date_add(l1.date,interval 1 DAY)
group by l1.date)t1
on t2.first_date=t1.date
group by t2.first_date ;t3

4.t3表的日期不全（因?yàn)椴淮_定每天都有新用戶，即t2表的日期不全，同樣也確定每天都有老用戶，t1表日期也不全，所以需要再連接原表，保留所有日期，用IFNULL補(bǔ)0
select distinct login.date, IFNULL(p,0)
from (select distinct t2.first_date ,round(savenum/count(t2.first_date),3) p
from (select user_id,min(date) first_date
from login
group by user_id)t2
join (select distinct l1.date,count(l1.user_id) savenum
from login l1
right join login l2
on l1.user_id=l2.user_id
where l2.date=date_add(l1.date,interval 1 DAY)
group by l1.date)t1
on t2.first_date=t1.date
group by t2.first_date
order by t2.first_date)t3
right join login
on login.date=t3.first_date
order by login.date