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

select 
    round(avg(total_amount),1) as avg_amount,
    round(avg(product_amount-total_amount),1) as avg_cost

from 
(
select 
    a.order_id as order_id,
    a.event_time as event_time,
    a.total_amount as total_amount,
    b.product_amount as product_amount,
    rank()over(partition by a.uid order by a.event_time) as rk
from 
    tb_order_overall a left join
(
    select 
        order_id,
        sum(price*cnt) as product_amount
    from 
        tb_order_detail
    group by 
        order_id
) b on a.order_id = b.order_id 
)c
where rk =1 and date_format(event_time,'%Y%m') = '202110'