专栏首页猴子聊数据分析图解面试题:人均付费如何分析?
原创

图解面试题:人均付费如何分析?

【题目】

现有表一:各城市用户ARPU值,表二:用户套餐费用

业务要求:

1.各地市用户数、总费用(ARPU之和)是多少?

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

3.表二中用户有重复的记录,找出重复的用户

【解题思路】

ARPU在业务指标中讲过是指“人均付费”

1.各地市用户数、总费用(ARPU之和)是多少?

当有“每个”出现的时候,要想到《猴子 从零学会SQL》中讲过的用分组汇总来实现该业务问题。

按照各城市(也就是每个城市)分组 (group by),统计用户数(计数函数count,加上distinct去掉重复的用户id)、总费用(求和函数sum)

select 城市,count(distinct 用户id) ,sum(ARPU值)from 各城市用户ARPU值group by 城市;

查询结果如下:

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

(1)各城市(每个城市),也就是按城市分组 (group by)

select 城市from 各城市用户ARPU值group by 城市;

(2)对用户的ARPU值进行分类,用case when 语句选出ARPU值符合分段区间并计数。例如,ARPU值在(0,30)范围内计数为1 ,代码如下:

select 城市,count(case when ARPU值>0 and ARPU值<30 then 1            else null           end ) as "(0,30)"from 各城市用户ARPU值group by 城市;

查询结果如下:

​和(0,30)一样,[30,50),[50-80),[80以上)我们只需要修改ARPU值判断条件即可。

select 城市,count(case when ARPU值>0 and ARPU值<30 then 1 else null end ) as "(0,30)",count(case when ARPU值>=30 and ARPU值<50 then 1 else null end ) as "[30,50)",count(case when ARPU值>=50 and ARPU值<80 then 1 else null end ) as "[50-80)",count(case when ARPU值>=80 then 1 else null end ) as "[80以上)"from 各城市用户ARPU值group by 城市;

最终查询结果如下:

3.表二中用户有重复的记录,找出重复的用户

表二中用户有重复的记录,请写出提取2条及以上用户的SQL语句

大白话理解为:找出有2条重复记录及以上用户。

按照用户(用户id)分组(group by),统计用户数(计数函数count)大于等于2条的就是重复记录(having 用户id >=2)

select 用户id,count(用户id)from 用户套餐费用表group by 用户idhaving count(用户id)>=2;

查询结果如下:

【本题考点】

1.遇到“每个”这种业务问题,要想到用“分组汇总”来解决。

2.查找重复数据,可以在分组汇总后,使用having对分组结果指定条件,如果汇总数据的值>=2就是重复数据。

3.对于分类,或者按条件的业务问题使用case来解决

【举一反三】

现在商家推出一款活动,如果用户将商品发到朋友圈,点赞数小于6的奖励零食;,点赞数大于等于6的奖励充电宝。下面是记录该活动的“活动表”。

找出哪些用户获得什么奖品。

【解题思路】

大白话为:根据用户(用户id)分组(group by),用case  when 语句选出点赞数的区间,符合并计数。

select 点赞的用户id,count(好友昵称),(case when count(好友昵称)>=6 then "充电宝"      else "零食"      end) as "奖品名称"from 活动表group by 点赞的用户id;

查询结果入下:

如何从零学会sql?

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

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

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

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

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

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

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

    猴子数据分析
  • 图解面试题:如何分析红包领取情况?

    “用户活跃表”记录了用户的登录信息,包括用户标识、用户登录日期,以及是否是新用户(如果是新注册的用户值为1;如果是老用户,值为0)。

    猴子聊数据分析
  • 收入下降,如何分析原因?

    某App 3月10日-3月12几天的整体充值收入提升非常明显(大于50%),但是,在整体充值中,占80%以上的功能充值的收入下降明显(大于50%)。这期间可能发...

    猴子聊数据分析
  • 怎么才有数据分析思路?

    看了其他高赞回答,给没有数据分析思路的朋友的感觉仍然是“你说的很有道理,但是我为啥还是没有数据分析思路?”。

    猴子数据分析

扫码关注云+社区

领取腾讯云代金券