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

with
    t1 as (
        select
            uid,
            min(event_time) as event_time
        from
            tb_order_detail
            left join tb_order_overall using (order_id)
        group by
            uid
        having
            date_format (min(event_time), '%Y-%m') = '2021-10'
    ),
    t2 as (
        select
            t1.uid,
            total_amount,
            order_id
        from
            t1
            left join tb_order_overall on t1.uid = tb_order_overall.uid
            and t1.event_time = tb_order_overall.event_time
    ),
    t4 as (
        select
            total_amount,
            sum_price,
            order_id
        from
            t2
            left join (
                select
                    order_id,
                    sum(price) as sum_price
                from
                    t2
                    left join tb_order_detail using (order_id)
                group by
                    order_id
            ) as t3 using (order_id)
    )
select
    round(avg(total_amount),1) as avg_amount,
    round(avg((sum_price - total_amount)),1) as avg_cost
from
    t4

shi山代码