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

select
    round(avg(total_amount), 1) as avg_amount
    , round(avg(cost), 1) as avg_cost
from(
    select
        oo.order_id
        , oo.uid
        , oo.total_amount
        , sum(od.price * od.cnt) - oo.total_amount as cost
    from tb_order_overall as oo
    inner join tb_order_detail as od on od.order_id = oo.order_id
    where (oo.uid, oo.order_id) in (
                    select
                        oo.uid
                        , min(oo.order_id) as min_order_id
                    from tb_order_overall as oo 
                    group by oo.uid
                    having date_format(min(oo.event_time), "%Y%m") = 202110
                    )
    group by oo.order_id, oo.uid, oo.total_amount
    ) as t