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

select 
round(sum(total_amount)/ count(order_id),1) avg_amount,
round(avg(cost),1) avg_cost
from
(select 
a.order_id order_id, 
total_amount,
sum(price*cnt)-total_amount cost
from tb_order_detail a
left join tb_order_overall b on a.order_id=b.order_id
left join tb_product_info c on a.product_id=c.product_id
where left(event_time,7) = '2021-10'
and (uid,event_time) in (select uid, min(event_time) event_time from tb_order_overall group by uid)
group by a.order_id, total_amount
) t2
筛选新用户易错点：
用下列代码作为新用户筛选条件是错误的
and timestampdiff(second,(select min(event_time) event_time from tb_order_overall),event_time) = 0
仅筛选最早下单的新用户而非所有新用户，应在筛选时间的同时加uid做限制，就可以筛选出所有新用户。