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

WITH t as(
SELECT od.order_id,product_id,oo.uid,DATE(event_time) AS dt,total_amount,price,cnt,status
FROM tb_order_overall AS oo
JOIN tb_order_detail AS od 
ON oo.order_id = od.order_id
),
t1 as(
SELECT uid,MIN(dt) AS min_dt
FROM t
WHERE status = 1
GROUP BY uid
HAVING YEAR(min_dt) = 2021 AND MONTH(min_dt) = 10
),
t2 as(
SELECT DISTINCT t.uid,t.order_id,total_amount,(price*cnt) AS total_price
FROM t JOIN t1 ON t.uid = t1.uid
WHERE dt = min_dt
),
t3 as(
SELECT uid,AVG(total_amount) AS total_amount,
SUM(total_price) AS total_cost
FROM t2
GROUP BY uid
)
SELECT 
ROUND(AVG(total_amount),1) AS avg_amount,
ROUND(AVG(total_cost) - AVG(total_amount),1) AS avg_cost
FROM t3

注意添加条件：WHERE dt = min_dt，确定是首单。 t.uid = t1.uid只能确保筛选出新用户，而这个用户可能存在好几个订单。