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

select
round(sum(b.total_amount)/count(b.uid),1) avg_amount,
round(sum(b.price)/count(b.uid),1) avg_cost
from
(
select 
a.uid,
max(too.total_amount) total_amount,
sum(tod.price)-max(too.total_amount) price
from 
(
select 
uid,
min(event_time) event_time
from tb_order_overall
group by uid
having month(min(event_time))=10 and year(min(event_time))=2021
) a
left join tb_order_overall too
on  too.uid=a.uid and too.event_time=a.event_time
left join tb_order_detail tod on too.order_id=tod.order_id
group by a.uid) b