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

-- 这题目和前几个题目有关联，现成的代码可以直接拿来用

-- 目前已知需要求 每个日期 新用户 的 次日留存率

-- 新用户次日留存=次日登录的新用户用户数/当日登录的新用户用户数。

-- 上一题已经让我算了每日新登录的用户数；

-- 判断首次登录 (user_id,date)in(select user_id,min(date)from login group by user_id)

-- 判断第二日登录 (user_id,date)in(select user_id,date(date,"+1 day")from login group by user_id)

-- 可以得到，统计第二日登录的表，*日期减去一天，换算成当日 代码：

select date(date,"-1 day") as date,sum(case when 
			(user_id,date)in
(SELECT user_id,date(min(date),"+1 day") from login group by user_id) then 1 else 0 end) as c2
			from login
            group by date-- 第二天登录的表

结果如下：

再利用上一题算的统计当天新用户数量的表，就可以进行解答了。

select 
day1.date,ifnull(round(day2.c2*1.000/day1.c1,3),0.000)-- 这边是转化浮点
from

(select date,
sum(case when 
(user_id,date) in (select user_id,min(date)from login group by user_id) then 1 else 0 end) as c1
from login group by date-- 统计第一日登录人数的表
)day1

left join -- 进行左连接，获取次日登录人数

(select date(date,"-1 day") as date,sum(case when 
(user_id,date)in(SELECT user_id,date(min(date),"+1 day") from login group by user_id) then 1 else 0 end) as c2 from login group by date-- 统计次日登录人数的表
)day2 on day1.date=day2.date