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

select 
round(avg(totalprice),1),
round(avg(buyprice-totalprice),1) from(
    
select distinct 
a.uid,
date(a.event_time) as t,
a.order_id,
    avg(a.total_amount)as totalprice,
sum(b.price*b.cnt) as buyprice,
sum(c.in_price*b.cnt) as inprice

from tb_order_overall a ,tb_order_detail b ,tb_product_info c 
where a.order_id=b.order_id 
and b.product_id=c.product_id 
and date(a.event_time)=
(select min(date(event_time)) from tb_order_overall d 
 where a.uid=d.uid)
 and a.order_id=
 (select min(order_id) from tb_order_overall e 
 where a.uid=e.uid and date(a.event_time)=date(e.event_time))
 group by a.uid,t,a.order_id
 )m where t like '2021-10%'