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

select round(sum(total_amount)/count(distinct uid),1)avg_amount,
round(sum(youhui)/count(distinct uid),1) avg_cost
from
(
select distinct uid,total_amount,sum(price*cnt)-total_amount youhui
from tb_order_overall oo
join tb_order_detail od using(order_id)
join tb_product_info pi using(product_id)
where event_time = 
(select min(oo2.event_time) 
from tb_order_overall oo2
where oo2.uid=oo.uid 
group by oo2.uid
) and date_format(event_time,"%Y%m")='202110'
group by uid, total_amount
)t1