牛客网SQL--某东篇

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

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

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

该题为简单题，一张表就可以解决
逻辑顺序为

• from 表 tb_order_overall
• 添加条件
  * 每月:GROUP BY DATE_FORMAT(time,"%Y-%m)
  * 大于十万
  * GMV为已付款订单和未付款订单两者之和:status IN (‘0’,‘1’)
  * 2021年: LIKE “2021%”
  * 升序
• 查找
  • 时间 DATE_FORMAT(event_time,"%Y-%m")
  • 每月GMV,要求整数： round(SUM(total_amount) ,0)

SELECT DATE_FORMAT(event_time,"%Y-%m"),
SUM(total_amount) st
FROM tb_order_overall
where status in ("0","1")
AND event_time LIKE "2021%"
GROUP BY DATE_FORMAT(event_time,"%Y-%m")
HAVING st>100000
ORDER BY st

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

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

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

• 时间：WHERE DATE_FORMAT(event_time,"%Y-%m") = “2021-10”
• 各个商品：GROUP BY product_id
• 各个比率：
  商品点展比 SUM(if_click) / COUNT(if_click)
  加购率 SUM(if_cart) / SUM(if_click)
  成单率 SUM(if_payment)/SUM(if_cart)
  退货率 SUM(if_refund)/ SUM(if_payment)
• 退货率不大于0.5 HAVING 退货率<=0.5
• order by product_id

SELECT product_id,
round(SUM(if_click)/COUNT(if_click),3) ctr,
round(SUM(if_cart)/SUM(if_click),3) cart_rate,
round(SUM(if_payment)/SUM(if_cart),3) pay_rate,
round(SUM(if_refund)/ SUM(if_payment),3) ref_rate
FROM tb_user_event
WHERE DATE_FORMAT(event_time,"%Y-%m") = "2021-10"
GROUP BY product_id
HAVING ref_rate <=0.5
order by ref_rate

某店铺的各商品毛利率及店铺整体毛利率



场景逻辑说明：

用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；

当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；

若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

注：商品毛利率=(1-进价/平均单件售价)*100%；
店铺毛利率=(1-总进价成本/总销售收入)*100%。
结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

解体思路

• 统计各个商品信息：

  GROUP BY product_id

  产品表连接订单表、连接订单详细表：表INNER JOIN表INNER JOIN表

  增加题目条件：
  日期 DATE_FORMAT(too.event_time,"%Y-%m") >= “2021-10”
  商店 tp.shop_id = 901

• 计算商品毛利率 a
  1-SUM(in_price * cnt)/SUM(price * cnt)
  不用 1-in_price/AVG(price) 是因为in_price会报FD错误

• 计算商店毛利路
  利用 ROLL UP 函数 对所有product生成 合计，就是商品毛利率

• 将合计的NULL 变为 店铺汇总，并将毛利率变为百分比
  IFNULL(product_id,“店铺汇总”）
  CONCAT(round(a * 100,1),"%")

• 排序
  order by 毛利率

SELECT IFNULL(t.pid,"店铺汇总"),concat(t.interest,"%") profit_rate
FROM
(SELECT tod.product_id pid,
round((1-SUM(in_price*cnt)/SUM(price*cnt))*100,1) interest
FROM tb_product_info tp INNER JOIN tb_order_detail tod ON tp.product_id = tod.product_id
                        INNER JOIN tb_order_overall too ON too.order_id = tod.order_id
WHERE DATE_FORMAT(too.event_time,"%Y-%m") >= "2021-10"
AND tp.shop_id = 901
GROUP BY tod.product_id
WITH ROLLUP
HAVING interest >= 24.9 OR pid IS NULL )t
order by t.pid

零食类商品中复购率top3高的商品
问题：请统计零食类商品中复购率top3高的商品。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

这个题我踩的坑是：复购率其实是先聚焦用户再看商品的一个比率，而我一开始就只是考虑的商品

解题思路

• 三表连接：因为要tag\product id\uid还有event time
• • 找到 各个用户 买的 各个零食
    tag = “零食”
    GROUP BY uid,product_id
• • 近九十天：event_time >=(SELECT DATE_SUB(max(event_time) INTERVAL 89 DAY) FROM )
• • 找到复购的产品 IF(COUNT(event_time) >1,1,0) repurchase
• 上面为一张表
• 复购率:
• SUM(repurchase) 复购人数
• 除以
• COUNT(repurchase) 购买人数
• 因为上一张表是group by uid , pid
  所以这个表有多个pid 故 group pid
  或者说产品复购率最终还是要回到产品上
• top3 + 复购率倒序、商品ID升序排序
• ORDER BY repurchase DESC , pid
• LIMIT 3

SELECT t.product_id,
ROUND(SUM(repurchase)/ COUNT(repurchase),3) re_rate
FROM
(SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    JOIN tb_product_info USING(product_id)
    WHERE tag="零食" AND event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
    GROUP BY uid, product_id)t
    GROUP BY t.product_id
    ORDER BY re_rate desc ,t.product_id
    limit 3 

10月的新户客单价和获客成本

SELECT ROUND(SUM(total_amount)/ COUNT(DISTINCT uid),1),ROUND(AVG(sump-total_amount),1)
FROM 
(SELECT order_id,sum(price*cnt) sump FROM tb_order_detail GROUP BY order_id) t1
INNER JOIN
(SELECT order_id, uid,total_amount,event_time,rank()over(partition by uid order by event_time) rnk
FROM tb_order_overall
)t2
ON t1.order_id = t2.order_id
WHERE rnk = 1
AND DATE_FORMAT(event_time,"%Y-%m") = "2021-10"