专栏首页猴子聊数据分析链家面试题:如何分析留存率?
原创

链家面试题:如何分析留存率?

​【面试题】

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

用户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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 图解面试题:如何分析游戏?

    猴子是一个班级的班主任,由于所带班级的学生成绩普遍不是很好。现在他需要找出每门课程中成绩最差的学生,然后有针对性的辅导。

    猴子聊数据分析
  • 图解面试题:如何分析中位数?

    学校每次考试完,都会有一个成绩表。例如,表中第1行表示编号为1的用户选择了C++岗位,该科目考了11001分。

    猴子数据分析
  • 图解面试题:双11用户如何分析?

    现有用户登录时间表,记录每个用户的id,姓名,邮箱地址和用户最后登录时间。表如下:

    猴子聊数据分析
  • 美团面试题:如何分析差评原因?

    需要分析导致分析差评的原因是什么,并给出改善方案。可以使用《猴子数据分析》里第6关讲过的分析方法,和第7-8关的如何用数据分析解决问题的框架来完成。

    猴子聊数据分析
  • 图解面试题:人均付费如何分析?

    2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少?

    猴子聊数据分析
  • 滴滴面试题:打车业务问题如何分析?

    公司的app(类似滴滴、uber)为用户提供打车服务。现有四张表,分别是“司机数据”表,“订单数据”表,“在线时长数据”表,“城市匹配数据”表。(滴滴面试题)

    猴子聊数据分析
  • 图解面试题:累计求和问题如何分析?

    其中,薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工。

    猴子聊数据分析
  • 小红书面试题:如何分析用户行为?

    小红书月活跃用户数已经过亿,用户在小红书上通过文字、图片、视频笔记分享生活,并创建相关商品链接,吸引相同爱好的用户进行收藏购买,用户的消费有什么行为特征呢?(小...

    猴子数据分析
  • 图解面试题:如何分析用户满意度?

    “满意度表”记录了教师和学生对课程的满意程度。“是否满意”列里是老师和学生对课程的评价,其中“是”表示教师和学生都满意。

    猴子数据分析

扫码关注云+社区

领取腾讯云代金券