题解 | #10月的新户客单价和获客成本#

SELECT 
	ROUND(SUM(total_amount)/COUNT(*),1) avg_amount,
	-- 总销售额/总人数 即为平均交易金额
	ROUND(SUM(total_discount)/COUNT(*),1) avg_cost
	-- 总优惠金额/总人数 即为平均获客成本
  FROM 
  (
	  SELECT 
		tb1.order_id,
		total_amount,
		total_money, 
		total_money-total_amount total_discount
	  FROM 
		  (
			  SELECT 
				uid,
				order_id,
                event_time,
		      		rank() over(PARTITION BY uid ORDER BY event_time) rk,
		      		-- 按下单时间升序，后面令rk=1,即首单
		      		-- 这里不进行group by之后再min()计算首单原因：group by之后最好是做聚合运算，如果SELECT数据，一般默认是第一条，那么所选数据就不一定是首单了
				total_amount 
			  FROM tb_order_overall oo
		  )tb1 -- 得到每个客户ID、订单ID、下单时间、下单时间排序、订单总价(实际金额)
	  LEFT JOIN 
		  (
			  SELECT order_id,SUM(price*cnt) total_money
			  FROM  tb_order_detail 
			  GROUP BY order_id
		  ) tb2 -- 得到客户订单号、以及本应该花费的钱(未优惠的)
	  ON tb1.order_id=tb2.order_id -- 将tb1和tb2连接起来
      WHERE rk = 1 
      -- rk=1主要是想筛选出最早的订单，即首单
      AND  DATE(event_time) BETWEEN '20211001' AND '20211031' 
      -- 要求是2021年10月的，一定不要在tb1就筛选出来了，会出错；因为来与tb2相连时就会缺少这部分数据(不会有所有的订单号)
  ) tb