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

1.获得新用户的登陆时间
select user_id,min(date) first_date
from login
group by user_id;t2

2.获得有第二天有老用户登陆的日期和人数
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,能够获得日期对应的 (老用户人数/新用户人数) 即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表的日期不全（因为不确定每天都有新用户，即t2表的日期不全，同样也确定每天都有老用户，t1表日期也不全，所以需要再连接原表，保留所有日期，用IFNULL补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