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

with t1 as (
    select
        uid
    from 
        tb_order_overall
    group by 
        uid
    having
        date_format(min(event_time),'%Y-%m') = '2021-10'
),

t2 as (
    select
        tb_order_overall.*,
        row_number() over (partition by tb_order_overall.uid order by event_time) as rn
    from 
        t1
    left join 
        tb_order_overall
    on 
        t1.uid = tb_order_overall.uid
)

select
    round(sum(total_amount) / count(*),1) as avg_amount,
    round(sum(sum_price - total_amount) / count(*),1) as avg_cost
from 
    t2
left join (
    select
        order_id,
        sum(temp_price) as sum_price
    from (
        select
            *,
            (price * cnt) as temp_price
        from 
            tb_order_detail
    ) as t
    group by
        order_id
) as t3
on 
    t2.order_id = t3.order_id
where 
    rn = 1