解一下TMD几道热门数据分析面试题。

最新推荐文章于 2023-01-06 09:01:39 发布

IT农民工1

最新推荐文章于 2023-01-06 09:01:39 发布

阅读量654

点赞数 1

本文转载自数据管道，详情可扫描下方二维码：

背景

Sql中有一类函数叫聚合函数，比如count、sum、avg、min、max等，这些函数的可以将多行数据按照规整聚集为一行，一般聚集前的数据行要大于聚集后的数据行。而有时候我们不仅想要聚集前的数据，又想要聚集后的数据，这时候便引入了窗口函数。

下面通过几道TMD面试题介绍一下如何使用窗口函数。涉及知识点有用于排序的窗口函数、用于用户分组查询的窗口函数、用于偏移分析的窗口函数，每种会通过一道面试题背景题解答。

正文

1、某顶尖外卖平台数据分析师面试题。现有交易数据表user_goods_table如下：

user_name 用户名
goods_kind 用户订购的的外卖品类

现在老板想知道每个用户购买的外卖品类偏好分布，并取出每个用户购买最多的外卖品类是哪个。

输出要求如下：

user_name 用户名
goods_kind 该用户购买的最多外卖品类

思路，利用窗口函数 row_number求得每个用户各自购买品类数量排行分布，并取出排行第一的品类即该用户购买最多的外卖品类。

参考题解：

select b.user_name,b.goods_kind from	
(select 	
user_name,	
goods_kind,	
row_number() over(partition by user_name 	
order by count(goods_kind) desc ) as rank 	
from user_goods_table) b where b.rank =1

2、某顶尖支付平台数据分析面试题。现有交易数据表user_sales_table如下：

user_name 用户名
pay_amount 用户支付额度

现在老板想知道支付金额在前20%的用户。

输出要求如下：

user_name 用户名（前10%的用户）

思路，利用窗口函数 ntile将每个用户和对应的支付金额分成5组（这样每组就有1/5），取分组排名第一的用户组即前支付金额在前20%的用户。（注意这里是求前20%的用户而不是求支付排在前20的用户）

参考题解：

select b.user_name from 	
(select 	
user_name,	
ntile(5) over(order by sum(pay_amount) desc) as level	
from user_sales_table group by user_name ) b 	
where b.level = 1

3、某顶尖小视频平台数据分析面试题。现有用户登陆表user_login_table如下：

user_name 用户名
date 用户登陆时间

现在老板想知道连续7天都登陆平台的重要用户。

输出要求如下：

user_name 用户名（连续7天都登陆的用户数）

思路，首先利用偏移窗口函数lead求得每个用户在每个登陆时间向后偏移7行的登陆时间，再计算每个用户在每个登陆时间滞后7天的登陆时间，如果每个用户向后偏移7行的登陆时间正好等于滞后7天的时间，说明该用户连续登陆了7天。

参考题解：

select b.user_name	
(select user_name,	
date,lead(date,7) 	
over(partition by user_name order by date desc) as date_7	
from user_login_table) b 	
where b.date is not null	
and date_sub(cast(b.date as date,7)) = cast(b.date_7 as date)

总结：

本文分别从3家数据分析面试题了解了窗口函数的实际应用场景，当然假设是大家都已知道窗口函数的语法，窗口函数的使用也确实可以衡量作为数据分析师对sql能力的掌握程度，当然不管是学习何种用法都要结合实际应用背景思考为何需要这种分析函数。

说明：由于参考题解是宝器在写文章的时候直接写出，如果存在错误请及时联系我，也欢迎各位与我讨论。

◆ ◆ ◆ ◆ ◆

长按二维码关注我们

数据森麟公众号的交流群已经建立，许多小伙伴已经加入其中，感谢大家的支持。大家可以在群里交流关于数据分析&数据挖掘的相关内容，还没有加入的小伙伴可以扫描下方管理员二维码，进群前一定要关注公众号奥，关注后让管理员帮忙拉进群，期待大家的加入。

管理员二维码：