一起来看看HQL窗口函数在大厂热门数据分析题中的运用

在大佬的公众号【数据管道】上看到三道数据分析题，转载如下：

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)