数据分析笔面试——SQL

1.小红书的一道笔试SQL题：
题目：
100家店铺，purchase表中存有销售记录，统计5月和6月，总gmv中，两个月分别的贡献前50%gmv的店铺名
purchase表的字段：id、dt、seller_id、seller_name、item_id、gmv
理解思路一：筛选5月和6月gmv排名top 50%
select concat(‘2019M’,dt) 月份,seller_name
from
(select dt,seller_name,
ntile(2) over(partition by dt order by gmv desc) r
from
(select month(dt) dt,seller_name,sum(gmv) gmv
from purchase
where month(dt) in(5,6)
group by month(dt),seller_name)t)u
where u.r=1;

理解思路二：筛选在总gmv中累计贡献前50%的店铺，内部排序默认为月gmv降序，否则没有太大的意义，我们关心哪些店铺在总gmv中贡献大，那么他本身的gmv排名也应该靠前
select concat(‘2019M’,dt) 月份,seller_name
from
(select dt,seller_name,
sum(gmv) over(partition by dt) sum1,
sum(gmv) over(partition by dt order by gmv desc) sum2
from
(select month(dt) dt,seller_name,sum(gmv) gmv
from purchase
group by month(dt),seller_name)t)u
where u.sum2/sum1 <=0.5

#2.随机取百分之五十的数据
select top 50 percent * from table2
order by newid();

每组取前百分之五十
select *
from
(select CONVERT(varchar(10),year,120) ‘月份’,am