小笨蛋看过来 | #10月的新户客单价和获客成本#

select
    round(sum(订单金额)/count(*),1) avg_amount,
    round(sum(优惠金额)/count(*),1) avg_cost
from
(
    select
        oo.order_id,
        date(event_time),
        total_amount 订单金额,
        sum(price*cnt) 商品总金额,
        sum(price*cnt)-total_amount 优惠金额
    from
        tb_order_overall oo join tb_order_detail od
    on 
        oo.order_id = od.order_id
    where 
        date_format(event_time, '%Y-%m') = '2021-10'
    and 
		(uid, event_time) in (select uid,min(event_time) from tb_order_overall group by uid)
    group by 
        oo.order_id, date(event_time),total_amount
) a