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

先上图 解题思路： 留存率=第n日新增在第n+1天留存的人数/第n日新增人数 因此，可以据此构建出两个表，分别求分子与分母然后在利用分母表左连接分子表（这样链接的目的是分母存在分子才可能存在），除此之外，还应注意分母表不能为0（使用ifnull）

分母表：

select* 
from (

			select count(a.user_id) as ct1,a.date
			from(

						SELECT user_id,
										date,
										ROW_NUMBER()over(partition by user_id ORDER BY date ) as rk
						from login
			)a
			where a.rk =1
			group by date
) tb1

分子表：

select *
 from (
		select count(b.user_id) as ct2,b.date
		from login b
		where (b.user_id,b.date) in (select user_id ,DATE_ADD(date,interval 1 day) from login)
		group by b.date

) tb2 on tb2.date-1=tb1.date

然后连接计算即可求出次日留存率，但此时，缺少留存率为0的日期，因此考虑union语法

SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
SELECT MIN(date) FROM login GROUP BY user_id)
ORDER BY date;

因此，完整代码为：

select tb1.date,ifnull(round(ct2/ct1,3),0) as p
from (

			select count(a.user_id) as ct1,a.date
			from(

						SELECT user_id,
										date,
										ROW_NUMBER()over(partition by user_id ORDER BY date ) as rk
						from login
			)a
			where a.rk =1
			group by date
) tb1

left join 
(
		select count(b.user_id) as ct2,b.date
		from login b
		where (b.user_id,b.date) in (select user_id ,DATE_ADD(date,interval 1 day) from login)
		group by b.date

) tb2 on tb2.date-1=tb1.date

union 

SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
SELECT MIN(date) FROM login GROUP BY user_id)
ORDER BY date;