链家面试题：如何分析留存率？
原创
【面试题】
手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。
用户id：用户唯一标识；
应用名称：是手机中的某个应用，例如相机、微信、大众点评等。
启动时长：某一天中使用某应用多长时间（分钟）。
启动次数：某一天中启动了某应用多少次。
登陆时间：使用手机的日期。例如2018-05-01。
现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：
某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）
指标定义：
某日活跃用户数，某日活跃的去重用户数。
N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。
N日活跃留存率，N日留存用户数/某日活跃用户数
例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%
所需获得的结果格式如下：
【解题思路】
本题中指标（用户留存数、留存率）是《猴子 业务指标》中讲过的常见业务指标，体现了某应用吸引用户的能力。
该业务分析要求查询结果中包括：日期（说明是按每天来汇总数据）、用户活跃数、N日留存数、N日留存率。
1.每天的活跃用户数
先来看活跃用户数这一列如何分析出？
活跃用户数对应的日期，表示每一行记录的是当天的活跃用户数。
当有“每个”出现的时候，要想到《猴子 从零学会SQL》中讲过的用分组汇总来实现该业务问题。
按每天（登陆时间）分组（group by ），统计应用（相机）每天的活跃用户数（计数函数count）。
select 登陆时间,count(distinct 用户id) as 活跃用户数 from 用户行为信息表 where 应用名称 ='相机' group by 登陆时间;查询结果如下：
2. 次日留存用户数
再来看查询结果中的次日留存用户数
次日留存用户数：在今日登录，明天也有登录的用户数。也就是时间间隔=1。
一个表如果涉及到时间间隔，就需要用到自联结，也就是将两个相同的表进行联结。
select a.用户id,a.登陆时间,b.登陆时间from 用户行为信息表 as a left join 用户行为信息表 as bon a.用户id = b.用户idwhere a.应用名称= '相机';联结后的临时表记为表c，那么如何从表c中查找出时间间隔（明天登陆时间-今天登陆时间）=1的数据呢？
（1）这涉及到计算两个日期之间的差值，《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。
select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔from c;用case语句选出时间间隔=1的数据，并计数就是次日留存用户数
count(distinct case when 时间间隔=1 then 用户id else null end) as 次日留存数代入上面的sql就是：
select *,count(distinct when 时间间隔=1 then 用户id else null end) as 次日留存数 from(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔from c)group by a.登陆时间;将临时表c的sql代入上面就得到了查询结果如下：
3.次日留存率
留存率=新增用户中登录用户数/新增用户数，所以次日留存率=次日留存用户数/当日用户活跃数
当日活跃用户数是count(distinct 用户id)
在上面分析次日留存数中，用次日留存用户数/当日用户活跃数就是次日留存率
select *,count(distinct when 时间间隔=1 then 用户id else null end) as 次日留存数 / count(distinct 用户id) as 次日留存率 from(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔from c) as dgroup by a.登陆时间;
将临时表c的sql代入就是：
查询结果：
4.三日的留存数，三日留存率,七日的留存数,七日留存率
和次日留存用户数，次日留存率分析思路一样，只需要更改时间间隔=N（日留存）即可。
最终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 bon a.用户id = b.用户idwhere a.应用名称= '相机') as c) as dgroup by a.登陆时间;查询结果：
【本题考点】
1.常用指标的理解，例如留存用户数、留存率。
2.灵活使用case来统计when 函数与group by 进行自定义列联表统计。
3.遇到只有一个表，但是需要计数时间间隔的问题，就要想到用自联结来求时间间隔，类似的有找出连续出现N次的内容、滴滴2020求职真题。
【举一反三】
链家2018春招笔试面试：现有订单表和用户表，格式字段如下图：
订单表 | 时间 | 订单id | 商品id | 用户id | 订单金额 |
用户表 | 用户id | 姓名 | 性别 | 年龄 |
1.查询2019年Q1季度，不同性别，不同年龄的成交用户数，成交量及成交金额
2.2019年1-4月产生订单的用户，以及在次月的留存用户数
【解题思路】
1.查询2019年Q1季度，不同性别，不同年龄的成交用户数，成交量及成交金额
根据性别、年龄进行分组，利用多表连接及聚合函数求出成交用户数，成交量及成交金额。
select b.性别,b.age, count(distinct a.用户id) as 用户数, count(订单id), sum(a.订单金额)from 订单表 as a inner join 用户表 as bon a.用户id = b.用户idwhere a.时间 between '2019-01-01' and '2019-03-31'group by b.性别,b.age; 2.2019年1-4月产生订单的用户，以及在次月的留存用户数
(1)用时间函数（timestampdiff）计算时间间隔，本题要求月份差，即用month
(2)用自联结计算时间间隔case when 计算符合个数并得出列的值。
select a.用户id,count(case when timestampdiff(month,b.时间,a.时间)=1 then a.用户id else null end) as 次月留存用户数from 订单表 as a inner join 订单表 as bon a.用户id = b.用户idwhere a.时间 between '2019-01-01' and '2019-04-30'group by a.用户id
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
