使用SQL输出次日成功的留存率、新登用户数、新用户的次日留存率

次日成功留存率

存在一个表：

输出新登录用户次日成功的留存率，即第一天登录后。第二天再次登录的概率。

代码：

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,date(min(date),'+1 day') from login group by user_id);

思路：（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率
总用户：

select count(distinct user_id) from login`

每个用户第一天登陆的日子：

select user_id,min(date) from login group by user_id`

在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期：

`select user_id,date(min(date),'+1 day') from login group by user_id`

最后使用count (distinct *) 可以得到各个部分的人数。

新登用户数

输出每个日期登录新用户个数：

每个日期里面用户登录的数目：

select l1.date,count(distinct l1.user_id)
from login l1
group by l1.date;

使用where字句找出新用户，思路是如果选取的日期正好等于用户登录的最小日期，而且用户id相同时，那么肯定就是这个日期登录的新用户：

select l1.date,count(distinct l1.user_id)
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date;

输出：

2020-10-12|3
2020-10-14|1

2020-10-13没有新用户登录，应该输出为0的，这个语句却没有输出。但是login表的日期是完整的，所以我们考虑将login表当主表，上面查出来的表左连接到主表，顺序输出，并使用ifnull语句将null变成0，最后再加上一个order by语句，就可以得到题目想要的结果了:

select login.date,ifnull(n1.new_num,0)
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date order by login.date

新用户的次日留存率

统计一下每个日期新用户的次日留存率：

先算出每天的新用户中有多少个第二天还登陆了（当天时间 – 第一次登录时间差值为1 ）

select o.date, sum(case  when o.sd =1 then 1 else 0 end) as n from 
(select user_id ,login.date ,
julianday(lead(login.date ,1,0)over(partition by user_id order by date))-julianday(min(date)over(partition by user_id order by date)) as sd 
 from login) o group by o.date

算出每天有多少个新用户：

select a.date ,sum(case a.date when a.fd then 1 else 0 end) as new
from 
(select user_id ,login.date ,min(login.date) over (partition by user_id order by login.date)as fd
from login) a group by a.date

将上面两步得出的表格进行连接，然后做除法算比率。（注意:0不能为分母要用case when 处理）

select c.date , round(1.0*c.n/ (case when b.new =0 then 1 else b.new end ),3)
from
(select o.date, sum(case when o.sd =1 then 1 else 0 end) as n from
(select user_id ,login.date ,
julianday(lead(login.date ,1,0) over(partition by user_id order by date))-julianday(min(date)over(partition by user_id order by date))
as sd from login) o group by o.date) c
left join
((select a.date ,sum(case a.date when a.fd then 1 else 0 end) as new from
(select user_id ,login.date ,min(login.date) over (partition by user_id order by login.date)as fd
from login) a group by a.date)) b
on c.date = b.date