SQL图解面试题:人均付费如何分析?(分组、条件汇总)

【题目】

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

 

 转载于公众号:猴子数据分析

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值