主要信息

  • 产品情况表product_tb(item_id指某款号的具体货号,style_id指款号,tag_price表示标签价格,inventory指库存量);销售数据表sales_tb(sales_date表示销售日期,user_id指用户编号,item_id指货号,sales_num表示销售数量,sales_price表示结算金额):
  • 请你统计每款的动销率(pin_rate,有销售的SKU数量/在售SKU数量)与售罄率(sell-through_rate,GMV/备货值,备货值=吊牌价* 库存数),按style_id升序排序

问题拆解

总体思路

  • pin_rate = 有销售的SKU数量/在售SKU数量(包含在售但不一定有销量的SKU数量),SKU为商品的款式即style_id,有销售的SKU数量 = 备货SKU数量 - 在售SKU数量,即分别求备货SKU数量和在售SKU数量
  • 求售罄率 = GMV(商品销售总额)/ 备货值 = GMV(商品销售总额)/(吊牌价* 库存数),最后根据style_id升序排列

具体实现

  • pin_rate
    • SKU通过style_id区分,根据不同style_id分组统计其销售数量和实际销售额 注:style_id在product_tb中,需要通过item_id与sales_tb连接拿到并统计实际销售额度和实际销售数量
    • 备货SKU数量 = sum(product_tb.inventory) 对所有的库存商品数量求和;
  • 求售罄率
    • 备货商品总价格 = 商品价格 * inventoryGMV = SUM(sales_tb.sales_price)
    • GMV(商品销售总额)/ 备货值 = GMV(商品销售总额),style_id升序排列 order by style_id

主要考察的点:group by、join、round、order by的基础使用,以及题目理解

SELECT style_id
	, round(onSale_num / (total_inventory - onSale_num) * 100, 2) AS 'pin_rate(%)'
	, round(GMV / 备货值 * 100, 2) AS 'sell-through_rate(%)'
FROM (
	SELECT style_id, SUM(sales_num) AS onSale_num, SUM(sales_price) AS GMV
	FROM sales_tb s
		LEFT JOIN product_tb p ON s.item_id = p.item_id
	GROUP BY style_id
) t1
	LEFT JOIN (
		SELECT style_id, SUM(inventory) AS total_inventory
			, SUM(tag_price * inventory) AS 备货值
		FROM product_tb
		GROUP BY style_id
	) t2 USING (style_id)
ORDER BY style_id;