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

select round(sum(total_amount)/count(1),1) avg_amount,round(sum(money-total_amount)/count(1),1) avg_cost
from(
select tod.order_id,uid,total_amount,sum(price*cnt) money
from tb_order_overall too
join tb_order_detail tod
on too.order_id = tod.order_id
where  (uid,event_time) in (select uid,min(event_time) from tb_order_overall 
group by uid)  ---筛选所有用户首次下单时间
and left(event_time,7) = '2021-10' ---筛选十月下单新用户
group by uid,tod.order_id,total_amount) as t1