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

#1.平均首单的客单价：订单总金额/订单数
#SELECT
#ROUND(SUM(total_amount)/COUNT(DISTINCT uid),1) AS avg_amount
#FROM 
#tb_order_overall
#WHERE WHERE DATE_FORMAT(event_time,'%Y-%m')='2021-10'

#2.平均获客成本：订单总优惠金额/订单数
#SELECT
#ROUND((SUM(t2.total_price)-SUM(t1.total_amount))/COUNT(DISTINCT t1.order_id),1) AS avg_cost
#FROM 
#tb_order_overall  t1 JOIN
#(SELECT order_id,SUM(price) AS total_price FROM tb_order_detail GROUP BY #order_id) t2
#ON t1.order_id=t2.order_id
#WHERE DATE_FORMAT(event_time,'%Y-%m')='2021-10'

#3.整合+完善筛选条件
SELECT
ROUND(SUM(t1.total_amount)/COUNT(DISTINCT t1.uid),1) AS avg_amount,
ROUND((SUM(t2.total_price)-SUM(t1.total_amount))/COUNT(DISTINCT t1.order_id),1) AS avg_cost
FROM 
tb_order_overall  t1 JOIN
(SELECT order_id,SUM(price) AS total_price FROM tb_order_detail GROUP BY order_id) t2
ON t1.order_id=t2.order_id
WHERE DATE_FORMAT(event_time,'%Y-%m')='2021-10'
AND (uid,DATE(event_time)) in 
(SELECT uid,MIN(DATE(event_time)) from tb_order_overall GROUP BY uid) -- 新用户得一次购买的时间