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

select round(avg(total_amount),1), round(avg(b-total_amount),1)
from

(select order_id, total_amount
from(
select order_id, uid, event_time, total_amount,
min(event_time)over(partition by uid) a
from tb_order_overall) as tb1
where a=event_time and date_format(event_time,'%Y%m')='202110') as tb2

left join 

(select order_id, sum(price*cnt) b
from tb_order_detail
group by order_id) as tb3
using(order_id)

思路：
①准备两张表，一张表选出新客户首单在2021年10月的数据，tb2；另一张表根据订单分组算出每单的实际支付金额，tb3；
②将tb2左联tb3，计算平均客单价和平均获客成本即可。