链家面试题:如何分析留存率?
【面试题】
手机中的相机是深受大家喜爱的应用之一,下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。
用户 id:用户唯一标识;
应用名称:是手机中的某个应用,例如相机、微信、大众点评等。
启动时长:某一天中使用某应用多长时间(分钟)。
启动次数:某一天中启动了某应用多少次。
登陆时间:使用手机的日期。例如 2018-05-01。
现在该手机厂商想要分析手机中的应用(相机)的活跃情况,需统计如下数据:
某日活跃用户(用户 id)在后续的一周内的留存情况(计算次日留存用户数,3 日留存用户数,7 日留存用户数)
指标定义:
某日活跃用户数,某日活跃的去重用户数。
N 日活跃用户数,某日活跃的用户数在之后的第 N 日活跃用户数。
N 日活跃留存率,N 日留存用户数 / 某日活跃用户数
例:登陆时间(20180501 日)去重用户数 10000,这批用户在 20180503 日仍有 7000 人活跃,则 3 日活跃留存率为 7000/10000=70%
所需获得的结果格式如下:
本题中指标(用户留存数、留存率)是常见业务指标,体现了某应用吸引用户的能力。
该业务分析要求查询结果中包括:日期(说明是按每天来汇总数据)、用户活跃数、N 日留存数、N 日留存率。
- 每天的活跃用户数
先来看活跃用户数这一列如何分析出?
活跃用户数对应的日期,表示每一行记录的是当天的活跃用户数。
当有 “每个” 出现的时候,要想到讲过的用分组汇总来实现该业务问题。
按每天(登陆时间)分组(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 b
on a.用户id = b.用户id
where 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);
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);
将临时表 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 b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a.登陆时间;
【本题考点】
常用指标的理解,例如留存用户数、留存率。
灵活使用 case 来统计 when 函数与 group by 进行自定义列联表统计。
遇到只有一个表,但是需要计数时间间隔的问题,就要想到用自联结来求时间间隔,类似的有找出连续出现 N 次的内容、滴滴 2020 求职真题。
【举一反三】
链家 2018 春招笔试面试:现有订单表和用户表,格式字段如下图:
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 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 计算符合个数并得出列的值。
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