牛客网SQL大厂真题二刷小白白话总结（三）电商场景（某东商城）

三、电商场景（某东商城）

目录

SQL13 计算商城中2021年每月的GMV（简单）

SQL14 统计2021年10月每个退货率不大于0.5的商品各项指标（中等）

SQL15 某店铺的各商品毛利率及店铺整体毛利率（中等）

SQL16 零食类商品中复购率top3高的商品（中等）

SQL17 10月的新户客单价和获客成本（较难）

---

SQL13 计算商城中2021年每月的GMV（简单）

select date_format(event_time,"%Y-%m") as month,
sum(total_amount) as GMV
from 
tb_order_overall
where status!=2 and year(event_time)=2021
group by month
having GMV>100000
order by GMV

很简单，就是限制条件不要遗漏就可以啦

SQL14 统计2021年10月每个退货率不大于0.5的商品各项指标（中等）

select product_id,round(ctr,3) as ctr,
round(cart_rate,3) as cart_rate,
round(payment_rate,3) as payment_rate,
round(refund_rate,3) as refund_rate
from 
(select product_id,sum(if_click)/count(*) as ctr,
 if(sum(if_click)!=0,sum(if_cart)/sum(if_click),0) as cart_rate,
 if(sum(if_cart)!=0,sum(if_payment)/sum(if_cart),0) as payment_rate,
 if(sum(if_payment)!=0,sum(if_refund)/sum(if_payment),0) as refund_rate
 from tb_user_event
 where date_format(event_time,"%Y-%m")='2021-10'
 group by product_id
 having refund_rate<=0.5 or sum(if_payment)=0) x
 order by product_id

这一道题主要是对于分母为0的数据也要考虑到（但实际操作好像考不考虑答案都没区别）

日期=后面不要忘记加“”（这是我一开始报错的原因）