> For the complete documentation index, see [llms.txt](https://houzidata.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://houzidata.gitbook.io/sql/di-3-zhang-duo-biao-cha-xun/lian-jie-mian-shi-ti-ru-he-fen-xi-liu-cun-lv.md).

# 链家面试题：如何分析留存率？

![](/files/-MV5j47ZN8pUy4l_KSRs)

**【面试题】**

手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。

![](/files/-MV5jBBpMrcni_HLs6KU)

用户 id：用户唯一标识；

应用名称：是手机中的某个应用，例如相机、微信、大众点评等。

启动时长：某一天中使用某应用多长时间（分钟）。

启动次数：某一天中启动了某应用多少次。

登陆时间：使用手机的日期。例如 2018-05-01。

现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：

某日活跃用户（用户 id）在后续的一周内的留存情况（计算次日留存用户数，3 日留存用户数，7 日留存用户数）

指标定义：

某日活跃用户数，某日活跃的去重用户数。

N 日活跃用户数，某日活跃的用户数在之后的第 N 日活跃用户数。

N 日活跃留存率，N 日留存用户数 / 某日活跃用户数

例：登陆时间（20180501 日）去重用户数 10000，这批用户在 20180503 日仍有 7000 人活跃，则 3 日活跃留存率为 7000/10000=70%

所需获得的结果格式如下：

![](/files/-MV5jEe_MsEI4JTk58qv)

![](/files/-MV5jL9WlZpZeV8X2fFo)

**【解题思路】**

本题中指标（用户留存数、留存率）是[《猴子 业务指标》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649248793\&idx=2\&sn=1fe86f3083bd9c8ad1a427898199c71c\&chksm=835fdc29b428553fabf47f23dc05f8f6c296cbcc06ce7e409ff712e81141fbf9ad0aa9b2b99d\&scene=21#wechat_redirect)中讲过的常见业务指标，体现了某应用吸引用户的能力。

该业务分析要求查询结果中包括：日期（说明是按每天来汇总数据）、用户活跃数、N 日留存数、N 日留存率。

**1. 每天的活跃用户数**

先来看活跃用户数这一列如何分析出？

![](/files/-MV5jRf1OoSMX0UWsEHG)

活跃用户数对应的日期，表示每一行记录的是当天的活跃用户数。

当有 “每个” 出现的时候，要想到[《猴子 从零学会 SQL》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)中讲过的用分组汇总来实现该业务问题。

按每天（登陆时间）分组（group by ），统计应用（相机）每天的活跃用户数（计数函数 count）。

```sql
select 登陆时间,count(distinct 用户id) as 活跃用户数 
from 用户行为信息表 
where 应用名称 ='相机' 
group by 登陆时间;
```

查询结果如下：

![](/files/-MV5jWKT6bo3WWrwrgik)

**2. 次日留存用户数**

再来看查询结果中的次日留存用户数

![](/files/-MV5jZpIxMc0pWli8WRq)

次日留存用户数：在今日登录，明天也有登录的用户数。也就是**时间间隔 = 1**。

一个表如果涉及到时间间隔，就需要用到自联结，也就是将两个相同的表进行联结。

![](/files/-MV5jcI7WG--4sPOOpih)

```sql
select a.用户id,a.登陆时间,b.登陆时间
from 用户行为信息表 as a  
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机';
```

联结后的临时表记为表 c，那么如何从表 c 中查找出时间间隔（明天登陆时间 - 今天登陆时间）=1 的数据呢？

（1）这涉及到计算两个日期之间的差值，[《猴子 从零学会 sql》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)里讲到对应单函数是 timestampdiff。下图是这个函数的用法。

![](/files/-MV5jhdoTd5EsStBZYWg)

```sql
select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from c;
```

用 case 语句选出时间间隔 = 1 的数据，并计数就是次日留存用户数

```sql
count(distinct case when 时间间隔=1 then 用户id
     else null
     end) as  次日留存数
```

代入上面的 sql 就是：

```sql
select *,count(distinct when 时间间隔=1 then 用户id
     else null
     end) as  次日留存数
 from
(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from c);
```

将临时表 c 的 sql 代入上面就得到了查询结果如下：

![](/files/-MV5jnrxpisHutAXFXjw)

**3. 次日留存率**

![](/files/-MV5jt2KganCqLZHX_NI)

留存率 = 新增用户中登录用户数 / 新增用户数，所以次日留存率 = 次日留存用户数 / 当日用户活跃数

当日活跃用户数是 `count(distinct 用户 id)`

在上面分析次日留存数中，用次日留存用户数 / 当日用户活跃数就是次日留存率

```sql
select *,count(distinct when 时间间隔=1 then 用户id
     else null
     end) as  次日留存数 / count(distinct 用户id) as 次日留存率
 from
(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from c);
```

![](/files/-MV5jwyMXxCIe3p13nQq)

将临时表 c 的 sql 代入就是：

![](/files/-MV5k36U212rul95IbmQ)

查询结果：

![](/files/-MV5k76jpbVXOUlXjbl6)

**4. 三日的留存数，三日留存率, 七日的留存数, 七日留存率**

![](/files/-MV5kBM3IhsnEN8AZQiT)

和次日留存用户数，次日留存率分析思路一样，只需要更改时间间隔 =N（日留存）即可。

最终 sql 代码如下：

```sql
select a.登陆时间,count(distinct a.用户id) as 活跃用户数,
count(distinct when 时间间隔=1 then 用户id else null end) as  次日留存数,
count(distinct when 时间间隔=1 then 用户id else null end) as  次日留存数 / count(distinct a.用户id) as 次日留存率,
count(distinct when 时间间隔=3 then 用户id else null end) as  三日留存数,
count(distinct when 时间间隔=3 then 用户id else null end) as  三日留存数 / count(distinct a.用户id) as 三日留存率,
count(distinct when 时间间隔=7 then 用户id else null end) as  七日留存数,
count(distinct when 时间间隔=7 then 用户id else null end) as  七日留存数 / count(distinct a.用户id) as 七日留存率
 from
(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from 
(select a.用户id,a.登陆时间,b.登陆时间
from 用户行为信息表 as a  
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a.登陆时间;
```

查询结果：

![](/files/-MV5kG45e_d4iWEuQ6Uz)

**【本题考点】**

1. [常用指标](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649248793\&idx=2\&sn=1fe86f3083bd9c8ad1a427898199c71c\&chksm=835fdc29b428553fabf47f23dc05f8f6c296cbcc06ce7e409ff712e81141fbf9ad0aa9b2b99d\&scene=21#wechat_redirect)的理解，例如留存用户数、留存率。
2. 灵活使用 case 来统计 when 函数与 group by 进行自定义列联表统计。
3. 遇到只有一个表，但是需要计数时间间隔的问题，就要想到用自联结来求时间间隔，类似的有[找出连续出现 N 次的内容](/sql/di-3-zhang-duo-biao-cha-xun/tu-jie-mian-shi-ti-zhao-chu-lian-xu-chu-xian-n-ci-de-nei-rong.md)、[滴滴 2020 求职真题](/sql/di-5-zhang-xiang-mu-shi-zhan/tu-jie-mian-shi-ti-di-di-2020-qiu-zhi-zhen-ti.md)。

**【举一反三】**

链家 2018 春招笔试面试：现有订单表和用户表，格式字段如下图：

| 订单表 | 时间 | 订单 id | 商品 id | 用户 id | 订单金额 |
| --- | -- | ----- | ----- | ----- | ---- |

| 用户表 | 用户 id | 姓名 | 性别 | 年龄 |
| --- | ----- | -- | -- | -- |

1.查询 2019 年 Q1 季度，不同性别，不同年龄的成交用户数，成交量及成交金额

2.2019 年 1-4 月产生订单的用户，以及在次月的留存用户数

**【解题思路】**

1.查询 2019 年 Q1 季度，不同性别，不同年龄的成交用户数，成交量及成交金额

根据性别、年龄进行分组，利用多表连接及聚合函数求出成交用户数，成交量及成交金额。

```sql
select b.性别,b.age,
       count(distinct a.用户id) as 用户数,
       count(订单id),
       sum(a.订单金额)
from 订单表 as a 
inner join 用户表 as b
on a.用户id = b.用户id
where a.时间 between '2019-01-01' and '2019-03-31'
group by b.性别,b.age;
```

2.2019 年 1-4 月产生订单的用户，以及在次月的留存用户数

(1) 用时间函数（timestampdiff）计算时间间隔，本题要求月份差，即用 month

(2) 用自联结计算时间间隔 case when 计算符合个数并得出列的值。

```sql
select a.用户id,
count(case when timestampdiff(month,b.时间,a.时间)=1 then a.用户id else null end)  as 次月留存用户数
from 订单表 as a 
inner join 订单表 as b
on a.用户id = b.用户id
where a.时间 between '2019-01-01' and '2019-04-30'
group by a.用户id
```

推荐：[如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)

![](/files/-MV5kca-VUEeON2x98-e)