sql 面试题4
解一下TMD几道热门数据分析面试题。
原创 爱德宝器 数据管道 2019-08-28
背景
Sql中有一类函数叫聚合函数，比如count、sum、avg、min、max等，这些函数的可以将多行数据按照规整聚集为一行，一般聚集前的数据行要大于聚集后的数据行。而有时候我们不仅想要聚集前的数据，又想要聚集后的数据，这时候便引入了窗口函数。
下面通过几道TMD面试题介绍一下如何使用窗口函数。涉及知识点有用于排序的窗口函数、用于用户分组查询的窗口函数、用于偏移分析的窗口函数，每种会通过一道面试题背景题解答。
正文
1、某顶尖外卖平台数据分析师面试题。现有交易数据表user_goods_table如下：
user_name 用户名
goods_kind 用户订购的的外卖品类
现在老板想知道每个用户购买的外卖品类偏好分布，并取出每个用户购买最多的外卖品类是哪个。
输出要求如下：
user_name 用户名
goods_kind 该用户购买的最多外卖品类
select user_name, goods_kind, rank() over (partition by user_name order by goods_kind desc) as rank_
from user_goods_table
where rank_ =1
写错了，因为where是先执行，select后执行的。而且orderby 用的不是count
修改：
select a.user_name,a.goods_kind
from
(select user_name, goods_kind, rank() over (partition by user_name order by count(goods_kind) desc) as rank_
from user_goods_table)a
where a.rank_ =1
另外答案用的row_number 1234
我用rank就是1223
思路，利用窗口函数 row_number求得每个用户各自购买品类数量排行分布，并取出排行第一的品类即该用户购买最多的外卖品类。
2、某顶尖支付平台数据分析面试题。现有交易数据表user_sales_table如下：
user_name 用户名
pay_amount 用户支付额度
现在老板想知道支付金额在前20%的用户。
输出要求如下：
user_name 用户名（前10%的用户）
row_number() over (partition by user_name order by sum(pay_amount) )
select a.user_name
(select user_name,sum(pay_amount) as sum,count(*) as count
from user_sales_table
group by 1)a
order by sum desc
limit 0,(count-1)*20%
答案 ：
select b.user_name from
(select
user_name,
ntile(5) over(order by sum(pay_amount) desc) as level -- 分成5组
from user_sales_table group by user_name ) b
where b.level = 1
不知 SELECT TOP 50 PERCENT * 能不能用
3、某顶尖小视频平台数据分析面试题。现有用户登陆表user_login_table如下：
user_name 用户名
date 用户登陆时间
现在老板想知道连续7天都登陆平台的重要用户。
输出要求如下：
user_name 用户名（连续7天都登陆的用户数）
select c.user_name
(select b.user_name, date_sub(b.date, interval a.rank_ day) as sub
(select user_name,date,row_number() over (partition by user_name order by date) as rank_ from user_login_table )a
inner join user_login_table b
on a.user_name=b.user_name
group by 1
having count(sub)>=7 )c
感觉写错了，并不需要a 和b
答案
思路，首先利用偏移窗口函数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)
这个部分我后来思考一下，应该是偏移6行。
比如说三天登陆的话，01，02，03都登录就算连续三天。那01到03就偏移两行就可以了。
