9、刷牛客网SQL题（三）

题目来源

牛客网SQL大厂真题——电商场景（某东商城）

---

一、SQL1（简单）

1.题目内容

描述
现有订单总表tb_order_overall

场景逻辑说明：
用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；
当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。

注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。

输出示例：
示例数据输出如下：

解释：
2021年10月有3笔已付款的订单，1笔未付款订单，总交易金额为109800；2021年11月有2笔已付款订单，1笔未付款订单，
总交易金额为111900（还有1笔退款订单由于已计算了付款的订单金额，无需计算在GMV中）。

2.思路分析

题目要求的是2021年每月的GMV，所以首先要根据event_time筛选出来2021年的数据，又因为GMV是由已付款订单和未付款订单两者之和组成的，所以还要筛选出来status为1和0的数据。筛选出来后，根据年—月进行分组求和即可。

3.语句实现

select
    date_format(event_time,'%Y-%m') month,
    sum(abs(total_amount)) GMV
from tb_order_overall
where year(event_time)='2021' and (status=1 or status=0)
group by date_format(event_time,'%Y-%m')
having GMV>100000
order by GMV

二、SQL1（简单）

1.题目内容

描述
现有用户对展示的商品行为表tb_user_event

问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，

注：
商品点展比=点击数÷展示数；
加购率=加购数÷点击数；
成单率=付款数÷加购数；退货率=退款数÷付款数，
当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

输出示例：
示例数据的输出结果如下

解释：
在2021年10月商品8001被展示了6次，点击了5次，加购了4次，付款了3次，退款了1次，因此点击率为5/6=0.833，加购率为4/5=0.800，
成单率为3/4=0.750，退货率为1/3=0.333（保留3位小数）；

2.思路分析

题目要求的时2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，首先要注意以下几点：
1）有展示记录：每一条数据就是一个展示记录
2）退货率不大于0.5：退货率是退款数除以付款数，而这里付款数可能为0，所以要注意判断分母是否为0

首先，要根据event_time过滤出来时间为2021-10月份的数据，按商品id分组，count(*)就是展示数，count(if())结合if_click、if_cart、if_payment、if_refund这些字段可以分别求出来加购数、付款数、退款数，进而做除法就可以得到最后结果了，最后按商品id升序即可。（做除法时，先要判断分母是否为0）

3.语句实现

select
    product_id,
    round(click_count/total_count,3) ctr,
    round(if(click_count=0,0,cart_count/click_count),3) cart_rate,
    round(if(cart_count=0,0,pay_count/cart_count),3) payment_rate,
    round(if(pay_count=0,0,refund_count/pay_count),3) refund_rate
from
(
    select
        product_id