SQL业务查询—用户留存率

SQL学起来很简单，各种方法也是一看就懂，尽管刷了一些sql相关的题目，但大多都是停留在课后习题的层面，面试中遇到具体业务中的SQL题，能够理解意思、也能知道用什么方法，但就是写不出来。所以今天把sql在具体业务中常用的几种查询整理一下，做一个专题。

用户留存率

1. 什么是用户留存

在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。
这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。顾名思义，留存指的就是“有多少用户留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力。

2. 如何计算留存率

新增用户数：在某个时间段（一般为第一整天）新登录应用的用户数；
登录用户数：登录应用后至当前时间，至少登录过一次的用户数；
第N日留存：指的是新增用户日之后的第N日依然登录的用户占新增用户的比例
第1日留存率（即“次留”）：（当天新增的用户中，新增日之后的第1天还登录的用户数）/第一天新增总用户数；
第3日留存率：（当天新增的用户中，新增日之后的第3天还登录的用户数）/第一天新增总用户数；
第7日留存率：（当天新增的用户中，新增日之后的第7天还登录的用户数）/第一天新增总用户数；
第30日留存率：（当天新增的用户中，新增日之后的第30天还登录的用户数）/第一天新增总用户数；
注意：留存一般是离散的概念，不要求用户在N天内每天都登录

使用SQL计算用户留存率

1. 观察用户登陆（login）记录表

用户登陆（login）表包含4个字段，分别为id（每条记录的id）、user_id（用户的id）、client_id（客户端id）、date（登陆日期）

id	user_id	client_id	date
1	2	1	2020-10-12
2	3	2	2020-10-12
3	1	2	2020-10-12
4	2	2	2020-10-13
5	4	1	2020-10-13
6	1	2	2020-10-13
7	1	2	2020-10-14

2. 思路分析

我们先从简单的开始，先计算用户的次日留存率，我们通过观察login表能够得到：
user_id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
user_id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
故次日成功的留存率为 2/4=0.5

于是我们很容易就可以想到用 次日留存用户数/新增用户数就可以得到次日留存率，那么接下来就是来统计 次日留存用户数 和 新增用户数，次日留存用户数就是首日活跃并且次日也活跃的用户数量，那么我们是不是很容易想到将login表按照相同的user_id和活跃日期差值为1天两个条件进行左连接。

select a.user_id, a.date, b.user_id, b.date
from login a left join login b 
-- 按照相同的user_id 和 两个日期差值为1天两个条件进行左连接
on a.user_id = b.user_id and datediff(b.date, a.date) = 1

我们可以观察一下左连接后的结果

a.user_id	a.date	b.user_id	b.date
2	2020-10-12	2	2020-10-13
3	2020-10-12	None	None
1	2020-10-12	1	2020-10-13
2	2020-10-13	None	None
4	2020-10-13	None	None
1	2020-10-13	1	2020-10-14
1	2020-10-14	None	None

那么，接下来我们就可以从这个表中获取 次日留存用户数 和 新增用户数，注意统计这两个数据一定要去重哦。

select
-- 注意新增用户数和次日留存用户数要去重
count(distinct a.user_id) as 新增用户数, 
count(distinct b.user_id) as 次日留存用户数
from login a left join login b 
on a.user_id = b.user_id and datediff(b.date, a.date) = 1

查询结果如下：

新增用户数	次日留存用户数
2	4

最后，我们就可以根据这个查询结果进行来计算次日留存率啦！

-- 方法一
select round(t.次日留存用户数 / t.活跃用户数, 3) 
from (
	select 
		count(distinct a.user_id) as 活跃用户数, 
		count(distinct b.user_id) as 次日留存用户数
	from login a left join login b 
	on a.user_id = b.user_id and datediff(b.date, a.date) = 1
	) t

以上就是计算次日留存的完整思路和代码过程，当然这个题目还可以有其他的解法。

3. 多种解法

上面讲述了方法一，接下来给大家展示一下方法二和方法三，感兴趣的同学可以都尝试一下。

-- 方法二

-- 3.计算留存率
select round(count(t2.user_id)/count(t1.user_id), 3)
-- 1.统计新增用户的首次登陆时间，得到新增用户数
from (select user_id, min(date) as date from login group by user_id) as t1
-- 2.与login按照相同的user_id和日期相差1天进行左连接，得到次日留存用户数
left join login t2 
on t1.user_id = t2.user_id and datediff(t2.date, t1.date) = 1;

-- 方法三

select 
-- 2.用次日留存用户数/新增用户数就得到次日留存率
round(count(distinct user_id)/(select count(distinct user_id) from login), 3) 
from login
-- 1.查询（user_id, date）对在新增用户的表中有多少个，就是次日留存用户数
where (user_id, date) in 
(select user_id, date_add(min(date), interval 1 day) from login group by user_id)

这个题目来源于牛客网SQL68题，大家也可以去网站中实际的操作运营一下，看看能否刷过。

4. 计算用户的次日、三日、七日留存率

更多情况下，需要观察用户在多个时间维度上的留存率，所以需要同时计算次日、三日、七日甚至是30日的留存率，那么这个应该如何实现呢？直接上代码

select round(t.次日留存用户数 / t.活跃用户数, 3) as 次日留存率,
       round(t.三日留存用户数 / t.活跃用户数, 3) as 三日留存率,
       round(t.七日留存用户数 / t.活跃用户数, 3) as 七日留存率
from
      (select a.date
       		  count(distinct a.user_id) as 活跃用户数,
              count(distinct b.user_id) as 次日留存用户数,
              count(distinct c.user_id) as 三日留存用户数,
              count(distinct d.user_id) as 七日留存用户数
      from login a 
      left join login b on a.user_id = b.user_id and datediff(b.date, a.date) = 1
      left join login c on a.user_id = c.user_id and datediff(c.date, a.date) = 3
      left join login d on a.user_id = d.user_id and datediff(d.date, a.date) = 7) t

这就是对SQL业务查询中经常会用到的-用户留存率的分享，可能与实际业务问题的难度还有一定的差距，但是解决问题的基本思路大致是相似的，希望在自己记录的同时也能对大家有一些帮助。