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

![](/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)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET 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?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.