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

with t1 as (select order_id,total_amount ,sum(price*cnt)as pc
from 
(
select *,rank()over(PARTITION by uid order by event_time) as rk 
from tb_order_overall 
)t
JOIN tb_order_detail using(order_id) 
where rk=1 and left(event_time,7)='2021-10'
GROUP BY order_id,total_amount
)
select round(avg(total_amount),1) as avg_a,
round(avg(pc-total_amount),1) as avg_c
from t1