美文网首页
图解面试题:人均付费如何分析?

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

作者: 猴子数据分析 | 来源:发表于2020-07-21 07:17 被阅读0次

    【题目】

    现有表一:各城市用户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值groupby城市;

    查询结果如下:

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

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

    select城市from各城市用户ARPU值groupby城市;

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

    select城市,count(casewhenARPU值>0andARPU值<30then1           else nullend)as"(0,30)"from各城市用户ARPU值groupby城市;

    查询结果如下:

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

    select城市,count(casewhenARPU值>0andARPU值<30then1elsenullend)as"(0,30)",count(casewhenARPU值>=30andARPU值<50then1elsenullend)as"[30,50)",count(casewhenARPU值>=50andARPU值<80then1elsenullend)as"[50-80)",count(casewhenARPU值>=80then1elsenullend)as"[80以上)"from各城市用户ARPU值groupby城市;

    最终查询结果如下:

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

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

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

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

    select用户id,count(用户id)from用户套餐费用表groupby用户idhavingcount(用户id)>=2;

    查询结果如下:

    【本题考点】

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

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

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

    【举一反三】

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

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

    【解题思路】

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

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

    查询结果入下:

    推荐:如何从零学会sql?

    相关文章

      网友评论

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

          本文链接:https://www.haomeiwen.com/subject/atybkktx.html