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

with t as( //2021年10月份新用户首次订单信息
    select min(order_id) as "order_id"
    from tb_order_overall
    group by uid
    having date_format(min(event_time),'%Y-%m') = '2021-10'
)
select t2.avg_amount,
    round(((select sum(od.price * od.cnt)
     from tb_order_detail od
     where od.order_id in (select order_id from t)) / (select count(*) from t) - t2.avg_amount),1)
from
(select round(sum(oo.total_amount) / (select count(*) from t),1) as "avg_amount"
from tb_order_overall oo join t on oo.order_id = t.order_id) t2