【面试题】 手机中的相机是深受大家喜爱的应用之一,下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。 用户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)。 查询结果如下: 2. 次日留存用户数 再来看查询结果中的次日留存用户数 次日留存用户数:在今日登录,明天也有登录的用户数。也就是时间间隔=1。 一个表如果涉及到时间间隔,就需要用到自联结,也就是将两个相同的表进行联结。 联结后的临时表记为表c,那么如何从表c中查找出时间间隔(明天登陆时间-今天登陆时间)=1的数据呢? (1)这涉及到计算两个日期之间的差值,《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。 用case语句选出时间间隔=1的数据,并计数就是次日留存用户数 代入上面的sql就是: 将临时表c的sql代入上面就得到了查询结果如下: 3.次日留存率 留存率=新增用户中登录用户数/新增用户数,所以次日留存率=次日留存用户数/当日用户活跃数 当日活跃用户数是count(distinct 用户id) 在上面分析次日留存数中,用次日留存用户数/当日用户活跃数就是次日留存率 将临时表c的sql代入就是: 查询结果: 4.三日的留存数,三日留存率,七日的留存数,七日留存率 和次日留存用户数,次日留存率分析思路一样,只需要更改时间间隔=N(日留存)即可。 最终sql代码如下: 查询结果: 【本题考点】 1.常用指标的理解,例如留存用户数、留存率。 2.灵活使用case来统计when 函数与group by 进行自定义列联表统计。 3.遇到只有一个表,但是需要计数时间间隔的问题,就要想到用自联结来求时间间隔,类似的有找出连续出现N次的内容、滴滴2020求职真题。 【举一反三】 链家2018春招笔试面试:现有订单表和用户表,格式字段如下图: 订单表 时间 订单id 商品id 用户id 订单金额 用户表 用户id 姓名 性别 年龄 1.查询2019年Q1季度,不同性别,不同年龄的成交用户数,成交量及成交金额 2.2019年1-4月产生订单的用户,以及在次月的留存用户数 【解题思路】 1.查询2019年Q1季度,不同性别,不同年龄的成交用户数,成交量及成交金额 根据性别、年龄进行分组,利用多表连接及聚合函数求出成交用户数,成交量及成交金额。 2.2019年1-4月产生订单的用户,以及在次月的留存用户数 (1)用时间函数(timestampdiff)计算时间间隔,本题要求月份差,即用month (2)用自联结计算时间间隔case when 计算符合个数并得出列的值。 推荐:如何从零学会sql?select 登陆时间,count(distinct 用户id) as 活跃用户数 from 用户行为信息表 where 应用名称 ='相机' group by 登陆时间;
select a.用户id,a.登陆时间,b.登陆时间from 用户行为信息表 as a left join 用户行为信息表 as bon a.用户id = b.用户idwhere a.应用名称= '相机';
select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔from c;
count(distinct case when 时间间隔=1 then 用户id else null end) as 次日留存数
select *,count(distinct when 时间间隔=1 then 用户id else null end) as 次日留存数 from(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔from c)group by a.登陆时间;
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.登陆时间;
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.登陆时间;
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;
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