每个日期新用户的次日留存率

SQL练习题：网站每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。

题目：

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。

有一个登录(login)记录表，简况如下:

 

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户

。。。

第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户

。。

最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

 

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

 

查询结果表明:

2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;

2020-10-13没有新用户登录，输出0.000;

2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;

2020-10-15没有新用户登录，输出0.000;

(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

程序：

1)求出新用户第一天登录日期

SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id

2) 算出有新用户登录的日期的新用户留存率

SELECT a.date,ROUND(COUNT(DISTINCT login.user_id)/ COUNT(a.user_id),3) AS p
FROM (
SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id) AS a
LEFT JOIN login
ON login.user_id=a.user_id
AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY a.date

 3)用UNION补上没有新用户登录的日期

SELECT a.date
	, ROUND(COUNT(b.user_id) * 1.0 / COUNT(a.user_id), 3) AS p
FROM (
	SELECT user_id, MIN(date) AS date
	FROM login
	GROUP BY user_id
) a
	LEFT JOIN login b
	ON a.user_id = b.user_id
		AND b.date = date_add(a.date, INTERVAL +1 DAY)
GROUP BY a.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;