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(varc