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

with
    t1 as (
        select
            uid,
            min(event_time) event_time
        from
            tb_order_overall
        group by
            uid
    )
select distinct
    round(
        (sum(avg(total_amount)) over ()) / sum(count(distinct a.uid)) over (),
        1
    ) avg_amount,
    round(
        (
            (sum(sum(price * cnt)) over ()) - (sum(avg(total_amount)) over ())
        ) / (sum(count(distinct a.uid)) over ()),
        1
    ) avg_cost
from
    tb_order_overall a
    join t1 on a.uid = t1.uid
    and a.event_time = t1.event_time
    join tb_order_detail b on a.order_id = b.order_id
where
    date_format (a.event_time, '%Y-%m') = '2021-10'
group by
    a.uid
limit
    1